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/func.sgml | 30 +++++++++++----- doc/src/sgml/queries.sgml | 77 ++++++++++++++++++++++++++++++++++------ doc/src/sgml/ref/select.sgml | 83 +++++++++++++++++++++++++++++++++----------- 3 files changed, 151 insertions(+), 39 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a5c808effae..a411e3a0cc9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11185,6 +11185,21 @@ SELECT NULLIF(value, '(none)') ... 1 2(2 rows) + + + + unnest(anyarray, anyarray [, ...]) + + + setof anyelement, anyelement [, ...] + expand multiple arrays (possibly of different types) to a set + of rows. This is only allowed in the FROM clause; see + + unnest(ARRAY[1,2],ARRAY['foo','bar','baz']) + 1 foo +2 bar +NULL baz(3 rows) + @@ -13295,6 +13310,8 @@ AND functions, as detailed in and . Other, more specialized set-returning functions are described elsewhere in this manual. + See for ways to combine multiple + set-returning functions. @@ -13499,14 +13516,11 @@ SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); - 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. + 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(). -- set returning function WITH ORDINALITY diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index c32c8576518..b33de682005 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -643,21 +643,55 @@ FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner - as a table, view, or subquery column. + as columns of a table, view, or subquery. - If a table function returns a base data type, the single result - column name matches the function name. If the function returns a - composite type, the result columns get the same names as the - individual attributes of the type. + Table functions may also be combined using the TABLE + syntax, with the results returned in parallel columns; the number of + result rows in this case is that of the largest function result, with + smaller results padded with NULLs to match. + +function_call WITH ORDINALITY AS table_alias (column_alias , ... ) +TABLE( function_call , ... ) WITH ORDINALITY AS table_alias (column_alias , ... ) + + + + If the WITH ORDINALITY clause is specified, an + additional column of type bigint will be added to the + function result columns. This column numbers the rows of the function + result set, starting from 1. (This is a generalization of the + SQL-standard syntax for UNNEST ... WITH ORDINALITY.) + By default, the ordinal column is called ordinality, but + a different column name can be assigned to it using + an AS clause. + + + + The special table function UNNEST may be called with + any number of array parameters, and it returns a corresponding number of + columns, as if UNNEST + () had been called on each parameter + separately and combined using the TABLE construct. + + + +UNNEST( array_expression , ... ) WITH ORDINALITY AS table_alias (column_alias , ... ) + + - A table function can be aliased in the FROM clause, - but it also can be left unaliased. If a function is used in the - FROM clause with no alias, the function name is used - as the resulting table name. + If no table_alias is specified, the function + name is used as the table name; in the case of a TABLE() + construct, the first function's name is used. + + + + If column aliases are not supplied, then for a function returning a base + data type, the column name is also the same as the function name. For a + function returning a composite type, the result columns get the names + of the individual attributes of the type. @@ -691,7 +725,30 @@ SELECT * FROM vw_getfoo; the pseudotype record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan - the query. Consider this example: + the query. This syntax looks like: + + + +function_call AS alias (column_definition , ... ) +function_call AS alias (column_definition , ... ) +TABLE( ... function_call AS (column_definition , ... ) , ... ) + + + + When not using the TABLE() syntax, + the column_definition list replaces the column + alias list that could otherwise be attached to the FROM + item; the names in the column definitions serve as column aliases. + When using the TABLE() syntax, + a column_definition list can be attached to + each member function separately; or if there is only one member function + and no WITH ORDINALITY clause, + a column_definition list can be written in + place of a column alias list following TABLE(). + + + + Consider this example: SELECT * FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc') 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