aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/func.sgml30
-rw-r--r--doc/src/sgml/queries.sgml77
-rw-r--r--doc/src/sgml/ref/select.sgml83
3 files changed, 151 insertions, 39 deletions
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)') ...
<entry><literallayout class="monospaced">1
2</literallayout>(2 rows)</entry>
</row>
+ <row>
+ <entry>
+ <literal>
+ <function>unnest</function>(<type>anyarray</type>, <type>anyarray</type> [, ...])
+ </literal>
+ </entry>
+ <entry><type>setof anyelement, anyelement [, ...]</type></entry>
+ <entry>expand multiple arrays (possibly of different types) to a set
+ of rows. This is only allowed in the FROM clause; see
+ <xref linkend="queries-tablefunctions"></entry>
+ <entry><literal>unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])</literal></entry>
+ <entry><literallayout class="monospaced">1 foo
+2 bar
+NULL baz</literallayout>(3 rows)</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -13295,6 +13310,8 @@ AND
functions, as detailed in <xref linkend="functions-srf-series"> and
<xref linkend="functions-srf-subscripts">. Other, more specialized
set-returning functions are described elsewhere in this manual.
+ See <xref linkend="queries-tablefunctions"> for ways to combine multiple
+ set-returning functions.
</para>
<table id="functions-srf-series">
@@ -13499,14 +13516,11 @@ SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
</indexterm>
<para>
- When a function in the <literal>FROM</literal> clause is suffixed by
- <literal>WITH ORDINALITY</literal>, a <type>bigint</type> 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 <literal>OUT</literal> parameters, but not when using
- a function returning <literal>RECORD</literal> with an explicit column
- definition list.
+ When a function in the <literal>FROM</literal> clause is suffixed
+ by <literal>WITH ORDINALITY</literal>, a <type>bigint</type> 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 <function>unnest()</>.
<programlisting>
-- 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 <literal>FROM</> clause of a query. Columns returned by table
functions can be included in <literal>SELECT</>,
<literal>JOIN</>, or <literal>WHERE</> clauses in the same manner
- as a table, view, or subquery column.
+ as columns of a table, view, or subquery.
</para>
<para>
- 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 <literal>TABLE</literal>
+ 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.
</para>
+<synopsis>
+<replaceable>function_call</replaceable> <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+TABLE( <replaceable>function_call</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+</synopsis>
+
+ <para>
+ If the <literal>WITH ORDINALITY</literal> clause is specified, an
+ additional column of type <type>bigint</type> 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 <literal>UNNEST ... WITH ORDINALITY</literal>.)
+ By default, the ordinal column is called <literal>ordinality</>, but
+ a different column name can be assigned to it using
+ an <literal>AS</literal> clause.
+ </para>
+
+ <para>
+ The special table function <literal>UNNEST</literal> may be called with
+ any number of array parameters, and it returns a corresponding number of
+ columns, as if <literal>UNNEST</literal>
+ (<xref linkend="functions-array">) had been called on each parameter
+ separately and combined using the <literal>TABLE</literal> construct.
+ </para>
+
+<synopsis>
+UNNEST( <replaceable>array_expression</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+</synopsis>
+
<para>
- A table function can be aliased in the <literal>FROM</> clause,
- but it also can be left unaliased. If a function is used in the
- <literal>FROM</> clause with no alias, the function name is used
- as the resulting table name.
+ If no <replaceable>table_alias</replaceable> is specified, the function
+ name is used as the table name; in the case of a <literal>TABLE()</>
+ construct, the first function's name is used.
+ </para>
+
+ <para>
+ 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.
</para>
<para>
@@ -691,7 +725,30 @@ SELECT * FROM vw_getfoo;
the pseudotype <type>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:
+ </para>
+
+<synopsis>
+<replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
+<replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
+TABLE( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
+</synopsis>
+
+ <para>
+ When not using the <literal>TABLE()</> syntax,
+ the <replaceable>column_definition</replaceable> list replaces the column
+ alias list that could otherwise be attached to the <literal>FROM</>
+ item; the names in the column definitions serve as column aliases.
+ When using the <literal>TABLE()</> syntax,
+ a <replaceable>column_definition</replaceable> list can be attached to
+ each member function separately; or if there is only one member function
+ and no <literal>WITH ORDINALITY</> clause,
+ a <replaceable>column_definition</replaceable> list can be written in
+ place of a column alias list following <literal>TABLE()</>.
+ </para>
+
+ <para>
+ Consider this example:
<programlisting>
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 ( <replaceable class="parameter">expression</replac
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
[ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
<replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
- [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
+ [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
+ [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
+ [ LATERAL ] TABLE( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
+ [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
<replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
@@ -368,30 +371,32 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
Function calls can appear in the <literal>FROM</literal>
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 <command>SELECT</command> command.
- When the optional <command>WITH ORDINALITY</command> 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:
-<programlisting>
-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)
-</programlisting>
- An alias can also be used. If an alias is written, a column
+ When the optional <command>WITH ORDINALITY</command> clause is
+ added to the function call, a new column is appended after
+ all the function's output columns with numbering for each row.
+ </para>
+
+ <para>
+ 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 <literal>ORDINALITY</literal>
if present.
- </para>
+ </para>
+
+ <para>
+ Multiple function calls can be combined into a
+ single <literal>FROM</>-clause item by surrounding them
+ with <literal>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.
+ </para>
<para>
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</replaceable> <optional>, ...
</>)</literal>. The column definition list must match the
actual number and types of columns returned by the function.
- <literal>ORDINALITY</literal> does not work in this case.
+ </para>
+
+ <para>
+ When using the <literal>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
+ <literal>TABLE( ... )</>. A column definition list can be placed
+ after the <literal>TABLE( ... )</> construct only if there's just a
+ single function and no <literal>WITH ORDINALITY</> clause.
+ </para>
+
+ <para>
+ To use <literal>ORDINALITY</literal> together with a column definition
+ list, you must use the <literal>TABLE( ... )</> syntax and put the
+ column definition list inside <literal>TABLE( ... )</>.
</para>
</listitem>
</varlistentry>
@@ -1599,6 +1618,23 @@ SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
</para>
<para>
+ Here is an example of a function with an ordinality column added:
+
+<programlisting>
+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)
+</programlisting>
+ </para>
+
+ <para>
This example shows how to use a simple <literal>WITH</> clause:
<programlisting>
@@ -1773,6 +1809,11 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
<productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
same as other set-returning functions.
</para>
+
+ <para>
+ Placing multiple function calls inside <literal>TABLE( ... )</> syntax is
+ also an extension of the SQL standard.
+ </para>
</refsect2>
<refsect2>
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-07 02:13:33 +0000