PostgreSQL 9.4 Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

背景

PostgreSQL 9.4 将支持SQL标准WITH ORDINALITY的写法,用于unnest以及其他查询相关的SQL中.

Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)  
Author: Andrew Gierth, David Fetter  
Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost  

测试可以下载如下源码, 已包含此部分更新 :

http://git.postgresql.org/gitweb/?p=postgresql.git;a=snapshot;h=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0;sf=tgz

在9.4以前的版本可以通过unnest_ordinality插件实现, 如下

http://pgxn.org/dist/unnest_ordinality/1.0.0/

查询中的用法说明, 紧跟在函数调用后面 :

function_name  
  
Function calls can appear in the FROM clause.   
(This is especially useful for functions that return result sets, but any function can be used.)   
This acts as though its output were created as a temporary table for the duration of this single SELECT command.   
When the optional WITH ORDINALITY is appended to the function call,   
a new column is appended after all the function call's columns with numbering for each row.   
  
For example:  
  
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;  
 unnest | ordinality   
--------+----------  
 a      |        1  
 b      |        2  
 c      |        3  
 d      |        4  
 e      |        5  
 f      |        6  
(6 rows)  
  
An alias can also be used. If an alias is written,   
a column alias list can also be written to provide substitute names for one or more attributes of the function's composite return type,   
including the column added by ORDINALITY if present.  
  
If the function has been defined as returning the record data type, then an alias or the key word AS must be present,   
followed by a column definition list in the form ( column_name data_type [, ... ]).   
The column definition list must match the actual number and types of columns returned by the function.   
ORDINALITY does not work in this case.  

目前ordinality不支持用在返回record类型的函数后.

测试 :

1. 函数测试

digoal=# select * from generate_series(1,4) with ordinality as g(g,o);  
 g | o   
---+---  
 1 | 1  
 2 | 2  
 3 | 3  
 4 | 4  
(4 rows)  
  
digoal=# select * from generate_series(1,4) with ordinality;  
 generate_series | ordinality   
-----------------+------------  
               1 |          1  
               2 |          2  
               3 |          3  
               4 |          4  
(4 rows)  
  
digoal=# select * from unnest(array[5,4,3,2,1]);  
 unnest   
--------  
      5  
      4  
      3  
      2  
      1  
(5 rows)  
  
digoal=# select * from unnest(array[5,4,3,2,1]) with ordinality;  
 unnest | ordinality   
--------+------------  
      5 |          1  
      4 |          2  
      3 |          3  
      2 |          4  
      1 |          5  
(5 rows)  

2. 不支持返回record类型函数 :

digoal=# create or replace function f_record() returns setof record as $$            
declare  
begin  
  return query select relname,relkind from pg_class limit 5;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
digoal=# select * from f_record() as (a name, b "char");  
            a            | b   
-------------------------+---  
 pg_statistic            | r  
 pg_type                 | r  
 pg_toast_2619           | t  
 pg_toast_2619_index     | i  
 pg_authid_rolname_index | i  
(5 rows)  
digoal=# select * from f_record() with ordinality as (a name, b "char");  
ERROR:  0A000: WITH ORDINALITY is not supported for functions returning "record"  
LINE 1: select * from f_record() with ordinality as (a name, b "char...  
                      ^  
LOCATION:  addRangeTableEntryForFunction, parse_relation.c:1292  

3. 其他查询测试

digoal=# SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;  
 r | i    
---+----  
 1 | 11  
 1 | 12  
 1 | 13  
 2 | 11  
 2 | 12  
 2 | 13  
 3 | 11  
 3 | 12  
 3 | 13  
(9 rows)  
digoal=# SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;  
 r | i  | o   
---+----+---  
 1 | 11 | 1  
 1 | 12 | 2  
 1 | 13 | 3  
 2 | 11 | 1  
 2 | 12 | 2  
 2 | 13 | 3  
 3 | 11 | 1  
 3 | 12 | 2  
 3 | 13 | 3  
(9 rows)  

详细测试可参考:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/test/regress/expected/rangefuncs.out;h=45ffd85b1b74715ea07b2f7995fc12ae64d85716;hp=16782776f45222b9b9ad75c5c776993baf5554dc;hb=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0;hpb=55cbfa5366b78d93cd1ff8c4c622b552985344f6

参考

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0

2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/test/regress/expected/rangefuncs.out;h=45ffd85b1b74715ea07b2f7995fc12ae64d85716;hp=16782776f45222b9b9ad75c5c776993baf5554dc;hb=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0;hpb=55cbfa5366b78d93cd1ff8c4c622b552985344f6

3. http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/func.sgml;h=34c5c2a2d6b00e7d00ef09e64fccc324f41c74b7;hp=528197e4bcc6072fa16a13a743a212158a078de3;hb=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0;hpb=55cbfa5366b78d93cd1ff8c4c622b552985344f6

4. http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/ref/select.sgml;h=42cfc28a5e52a25a5128540538b6deb4514465e2;hp=b0cec1421ca38411550463fba99a23bd75850093;hb=c62736cc37f6812d1ebb41ea5a86ffe60564a1f0;hpb=55cbfa5366b78d93cd1ff8c4c622b552985344f6

5. http://pgxn.org/dist/unnest_ordinality/1.0.0/

6. http://www.postgresql.org/docs/devel/static/sql-select.html

digoal’s 大量PostgreSQL文章入口