From 784e762e886e6f72f548da86a27cd2ead87dbd1c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 21 Nov 2013 19:37:02 -0500 Subject: Support multi-argument UNNEST(), and TABLE() syntax for multiple functions. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the ability to write TABLE( function1(), function2(), ...) as a single FROM-clause entry. The result is the concatenation of the first row from each function, followed by the second row from each function, etc; with NULLs inserted if any function produces fewer rows than others. This is believed to be a much more useful behavior than what Postgres currently does with multiple SRFs in a SELECT list. This syntax also provides a reasonable way to combine use of column definition lists with WITH ORDINALITY: put the column definition list inside TABLE(), where it's clear that it doesn't control the ordinality column as well. Also implement SQL-compliant multiple-argument UNNEST(), by turning UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)). The SQL standard specifies TABLE() with only a single function, not multiple functions, and it seems to require an implicit UNNEST() which is not what this patch does. There may be something wrong with that reading of the spec, though, because if it's right then the spec's TABLE() is just a pointless alternative spelling of UNNEST(). After further review of that, we might choose to adopt a different syntax for what this patch does, but in any case this functionality seems clearly worthwhile. Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and significantly revised by me --- doc/src/sgml/ref/select.sgml | 83 +++++++++++++++++++++++++++++++++----------- 1 file changed, 62 insertions(+), 21 deletions(-) (limited to 'doc/src/sgml/ref') diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index e603b7644ea..88ebd73d49c 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -52,9 +52,12 @@ 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 [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] + [ 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 [, ...] ) + [ LATERAL ] TABLE( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] ) + [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] and with_query is: @@ -368,30 +371,32 @@ TABLE [ ONLY ] table_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 + though the function's 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 + When the optional WITH ORDINALITY clause is + added to the function call, a new column is appended after + all the function's output columns with numbering for each row. + + + + An alias can be provided in the same way as for a table. + 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. - + + + + Multiple function calls can be combined into a + single FROM-clause item by surrounding them + with TABLE( ... ). The output of such an item is the + concatenation of the first row from each function, then the second + row from each function, etc. If some of the functions produce fewer + rows than others, NULLs are substituted for the missing data, so + that the total number of rows returned is always the same as for the + function that produced the most rows. + If the function has been defined as returning the @@ -402,7 +407,21 @@ SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; 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. + + + + When using the TABLE( ... ) syntax, if one of the + functions requires a column definition list, it's preferred to put + the column definition list after the function call inside + TABLE( ... ). A column definition list can be placed + after the TABLE( ... ) construct only if there's just a + single function and no WITH ORDINALITY clause. + + + + To use ORDINALITY together with a column definition + list, you must use the TABLE( ... ) syntax and put the + column definition list inside TABLE( ... ). @@ -1598,6 +1617,23 @@ SELECT * FROM distributors_2(111) AS (f1 int, f2 text); + + Here is an example of a function with an ordinality column added: + + +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) + + + This example shows how to use a simple WITH clause: @@ -1773,6 +1809,11 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; PostgreSQL treats UNNEST() the same as other set-returning functions. + + + Placing multiple function calls inside TABLE( ... ) syntax is + also an extension of the SQL standard. + -- cgit v1.2.3