From c62736cc37f6812d1ebb41ea5a86ffe60564a1f0 Mon Sep 17 00:00:00 2001 From: Greg Stark Date: Mon, 29 Jul 2013 16:38:01 +0100 Subject: Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF) Author: Andrew Gierth, David Fetter Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost --- doc/src/sgml/func.sgml | 44 ++++++++++++++++++++++++++++++++++++++++- doc/src/sgml/ref/select.sgml | 47 +++++++++++++++++++++++++++++++++----------- 2 files changed, 78 insertions(+), 13 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 528197e4bcc..34c5c2a2d6b 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -13278,7 +13278,7 @@ select $1[i][j] generate_subscripts($1,2) g2(j); $$ LANGUAGE sql IMMUTABLE; CREATE FUNCTION -postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); +SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); unnest2 --------- 1 @@ -13286,6 +13286,48 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); 3 4 (4 rows) + + + + + ordinality + + + + When a function in the FROM clause is suffixed by + WITH ORDINALITY, a bigint column is appended + to the output which starts from 1 and increments by 1 for each row of the + function's output. This is most useful in the case of set returning functions + such as UNNEST(). This functionality is available for functions returning + composite types or using OUT parameters, but not when using + a function returning RECORD with an explicit column + definition list. + + +-- set returning function WITH ORDINALITY +SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n); + ls | n +-----------------+---- + pg_serial | 1 + pg_twophase | 2 + postmaster.opts | 3 + pg_notify | 4 + postgresql.conf | 5 + pg_tblspc | 6 + logfile | 7 + base | 8 + postmaster.pid | 9 + pg_ident.conf | 10 + global | 11 + pg_clog | 12 + pg_snapshots | 13 + pg_multixact | 14 + PG_VERSION | 15 + pg_xlog | 16 + pg_hba.conf | 17 + pg_stat_tmp | 18 + pg_subtrans | 19 +(19 rows) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index b0cec1421ca..42cfc28a5e5 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -52,7 +52,8 @@ SELECT [ ALL | DISTINCT [ ON ( expressiontable_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] - [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] + [ LATERAL ] function_name ( [ argument [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] + [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] ) [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] @@ -368,18 +369,40 @@ TABLE [ ONLY ] table_name [ * ] 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. 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. 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 - ( 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. + class="parameter">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. -- cgit v1.2.3