Skip to content

Commit 784e762

Browse files
tglsfdctglsfdc
committed
Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
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
1 parent 38f4328 commit 784e762

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

48 files changed

+2639
-1203
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1456,7 +1456,7 @@ JumbleRangeTable(pgssJumbleState *jstate, List *rtable)
14561456
APP_JUMB(rte->jointype);
14571457
break;
14581458
case RTE_FUNCTION:
1459-
JumbleExpr(jstate, rte->funcexpr);
1459+
JumbleExpr(jstate, (Node *) rte->functions);
14601460
break;
14611461
case RTE_VALUES:
14621462
JumbleExpr(jstate, (Node *) rte->values_lists);
@@ -1866,6 +1866,13 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
18661866
JumbleExpr(jstate, setop->rarg);
18671867
}
18681868
break;
1869+
case T_RangeTblFunction:
1870+
{
1871+
RangeTblFunction *rtfunc = (RangeTblFunction *) node;
1872+
1873+
JumbleExpr(jstate, rtfunc->funcexpr);
1874+
}
1875+
break;
18691876
default:
18701877
/* Only a warning, since we can stumble along anyway */
18711878
elog(WARNING, "unrecognized node type: %d",

‎doc/src/sgml/func.sgml

Lines changed: 22 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -11185,6 +11185,21 @@ SELECT NULLIF(value, '(none)') ...
1118511185
<entry><literallayout class="monospaced">1
1118611186
2</literallayout>(2 rows)</entry>
1118711187
</row>
11188+
<row>
11189+
<entry>
11190+
<literal>
11191+
<function>unnest</function>(<type>anyarray</type>, <type>anyarray</type> [, ...])
11192+
</literal>
11193+
</entry>
11194+
<entry><type>setof anyelement, anyelement [, ...]</type></entry>
11195+
<entry>expand multiple arrays (possibly of different types) to a set
11196+
of rows. This is only allowed in the FROM clause; see
11197+
<xref linkend="queries-tablefunctions"></entry>
11198+
<entry><literal>unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])</literal></entry>
11199+
<entry><literallayout class="monospaced">1 foo
11200+
2 bar
11201+
NULL baz</literallayout>(3 rows)</entry>
11202+
</row>
1118811203
</tbody>
1118911204
</tgroup>
1119011205
</table>
@@ -13295,6 +13310,8 @@ AND
1329513310
functions, as detailed in <xref linkend="functions-srf-series"> and
1329613311
<xref linkend="functions-srf-subscripts">. Other, more specialized
1329713312
set-returning functions are described elsewhere in this manual.
13313+
See <xref linkend="queries-tablefunctions"> for ways to combine multiple
13314+
set-returning functions.
1329813315
</para>
1329913316

1330013317
<table id="functions-srf-series">
@@ -13499,14 +13516,11 @@ SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
1349913516
</indexterm>
1350013517

1350113518
<para>
13502-
When a function in the <literal>FROM</literal> clause is suffixed by
13503-
<literal>WITH ORDINALITY</literal>, a <type>bigint</type> column is appended
13504-
to the output which starts from 1 and increments by 1 for each row of the
13505-
function's output. This is most useful in the case of set returning functions
13506-
such as UNNEST(). This functionality is available for functions returning
13507-
composite types or using <literal>OUT</literal> parameters, but not when using
13508-
a function returning <literal>RECORD</literal> with an explicit column
13509-
definition list.
13519+
When a function in the <literal>FROM</literal> clause is suffixed
13520+
by <literal>WITH ORDINALITY</literal>, a <type>bigint</type> column is
13521+
appended to the output which starts from 1 and increments by 1 for each row
13522+
of the function's output. This is most useful in the case of set returning
13523+
functions such as <function>unnest()</>.
1351013524

1351113525
<programlisting>
1351213526
-- set returning function WITH ORDINALITY

‎doc/src/sgml/queries.sgml

Lines changed: 67 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -643,21 +643,55 @@ FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
643643
the <literal>FROM</> clause of a query. Columns returned by table
644644
functions can be included in <literal>SELECT</>,
645645
<literal>JOIN</>, or <literal>WHERE</> clauses in the same manner
646-
as a table, view, or subquery column.
646+
as columns of a table, view, or subquery.
647647
</para>
648648

649649
<para>
650-
If a table function returns a base data type, the single result
651-
column name matches the function name. If the function returns a
652-
composite type, the result columns get the same names as the
653-
individual attributes of the type.
650+
Table functions may also be combined using the <literal>TABLE</literal>
651+
syntax, with the results returned in parallel columns; the number of
652+
result rows in this case is that of the largest function result, with
653+
smaller results padded with NULLs to match.
654654
</para>
655655

656+
<synopsis>
657+
<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>
658+
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>
659+
</synopsis>
660+
661+
<para>
662+
If the <literal>WITH ORDINALITY</literal> clause is specified, an
663+
additional column of type <type>bigint</type> will be added to the
664+
function result columns. This column numbers the rows of the function
665+
result set, starting from 1. (This is a generalization of the
666+
SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.)
667+
By default, the ordinal column is called <literal>ordinality</>, but
668+
a different column name can be assigned to it using
669+
an <literal>AS</literal> clause.
670+
</para>
671+
672+
<para>
673+
The special table function <literal>UNNEST</literal> may be called with
674+
any number of array parameters, and it returns a corresponding number of
675+
columns, as if <literal>UNNEST</literal>
676+
(<xref linkend="functions-array">) had been called on each parameter
677+
separately and combined using the <literal>TABLE</literal> construct.
678+
</para>
679+
680+
<synopsis>
681+
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>
682+
</synopsis>
683+
656684
<para>
657-
A table function can be aliased in the <literal>FROM</> clause,
658-
but it also can be left unaliased. If a function is used in the
659-
<literal>FROM</> clause with no alias, the function name is used
660-
as the resulting table name.
685+
If no <replaceable>table_alias</replaceable> is specified, the function
686+
name is used as the table name; in the case of a <literal>TABLE()</>
687+
construct, the first function's name is used.
688+
</para>
689+
690+
<para>
691+
If column aliases are not supplied, then for a function returning a base
692+
data type, the column name is also the same as the function name. For a
693+
function returning a composite type, the result columns get the names
694+
of the individual attributes of the type.
661695
</para>
662696

663697
<para>
@@ -691,7 +725,30 @@ SELECT * FROM vw_getfoo;
691725
the pseudotype <type>record</>. When such a function is used in
692726
a query, the expected row structure must be specified in the
693727
query itself, so that the system can know how to parse and plan
694-
the query. Consider this example:
728+
the query. This syntax looks like:
729+
</para>
730+
731+
<synopsis>
732+
<replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
733+
<replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
734+
TABLE( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
735+
</synopsis>
736+
737+
<para>
738+
When not using the <literal>TABLE()</> syntax,
739+
the <replaceable>column_definition</replaceable> list replaces the column
740+
alias list that could otherwise be attached to the <literal>FROM</>
741+
item; the names in the column definitions serve as column aliases.
742+
When using the <literal>TABLE()</> syntax,
743+
a <replaceable>column_definition</replaceable> list can be attached to
744+
each member function separately; or if there is only one member function
745+
and no <literal>WITH ORDINALITY</> clause,
746+
a <replaceable>column_definition</replaceable> list can be written in
747+
place of a column alias list following <literal>TABLE()</>.
748+
</para>
749+
750+
<para>
751+
Consider this example:
695752
<programlisting>
696753
SELECT *
697754
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')

‎doc/src/sgml/ref/select.sgml

Lines changed: 62 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -52,9 +52,12 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
5252
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5353
[ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
5454
<replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
55-
[ 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> [, ...] ) ] ]
55+
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
56+
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5657
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
5758
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
59+
[ LATERAL ] TABLE( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
60+
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5861
<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> [, ...] ) ]
5962

6063
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
@@ -368,30 +371,32 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
368371
Function calls can appear in the <literal>FROM</literal>
369372
clause. (This is especially useful for functions that return
370373
result sets, but any function can be used.) This acts as
371-
though its output were created as a temporary table for the
374+
though the function's output were created as a temporary table for the
372375
duration of this single <command>SELECT</command> command.
373-
When the optional <command>WITH ORDINALITY</command> is
374-
appended to the function call, a new column is appended after
375-
all the function call's columns with numbering for each row.
376-
For example:
377-
<programlisting>
378-
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
379-
unnest | ordinality
380-
--------+----------
381-
a | 1
382-
b | 2
383-
c | 3
384-
d | 4
385-
e | 5
386-
f | 6
387-
(6 rows)
388-
</programlisting>
389-
An alias can also be used. If an alias is written, a column
376+
When the optional <command>WITH ORDINALITY</command> clause is
377+
added to the function call, a new column is appended after
378+
all the function's output columns with numbering for each row.
379+
</para>
380+
381+
<para>
382+
An alias can be provided in the same way as for a table.
383+
If an alias is written, a column
390384
alias list can also be written to provide substitute names for
391385
one or more attributes of the function's composite return
392386
type, including the column added by <literal>ORDINALITY</literal>
393387
if present.
394-
</para>
388+
</para>
389+
390+
<para>
391+
Multiple function calls can be combined into a
392+
single <literal>FROM</>-clause item by surrounding them
393+
with <literal>TABLE( ... )</>. The output of such an item is the
394+
concatenation of the first row from each function, then the second
395+
row from each function, etc. If some of the functions produce fewer
396+
rows than others, NULLs are substituted for the missing data, so
397+
that the total number of rows returned is always the same as for the
398+
function that produced the most rows.
399+
</para>
395400

396401
<para>
397402
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;
402407
class="parameter">data_type</replaceable> <optional>, ...
403408
</>)</literal>. The column definition list must match the
404409
actual number and types of columns returned by the function.
405-
<literal>ORDINALITY</literal> does not work in this case.
410+
</para>
411+
412+
<para>
413+
When using the <literal>TABLE( ... )</> syntax, if one of the
414+
functions requires a column definition list, it's preferred to put
415+
the column definition list after the function call inside
416+
<literal>TABLE( ... )</>. A column definition list can be placed
417+
after the <literal>TABLE( ... )</> construct only if there's just a
418+
single function and no <literal>WITH ORDINALITY</> clause.
419+
</para>
420+
421+
<para>
422+
To use <literal>ORDINALITY</literal> together with a column definition
423+
list, you must use the <literal>TABLE( ... )</> syntax and put the
424+
column definition list inside <literal>TABLE( ... )</>.
406425
</para>
407426
</listitem>
408427
</varlistentry>
@@ -1598,6 +1617,23 @@ SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
15981617
</programlisting>
15991618
</para>
16001619

1620+
<para>
1621+
Here is an example of a function with an ordinality column added:
1622+
1623+
<programlisting>
1624+
SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
1625+
unnest | ordinality
1626+
--------+----------
1627+
a | 1
1628+
b | 2
1629+
c | 3
1630+
d | 4
1631+
e | 5
1632+
f | 6
1633+
(6 rows)
1634+
</programlisting>
1635+
</para>
1636+
16011637
<para>
16021638
This example shows how to use a simple <literal>WITH</> clause:
16031639

@@ -1773,6 +1809,11 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
17731809
<productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
17741810
same as other set-returning functions.
17751811
</para>
1812+
1813+
<para>
1814+
Placing multiple function calls inside <literal>TABLE( ... )</> syntax is
1815+
also an extension of the SQL standard.
1816+
</para>
17761817
</refsect2>
17771818

17781819
<refsect2>

‎src/backend/access/common/tupdesc.c

Lines changed: 41 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -157,40 +157,6 @@ CreateTupleDescCopy(TupleDesc tupdesc)
157157
return desc;
158158
}
159159

160-
/*
161-
* CreateTupleDescCopyExtend
162-
* This function creates a new TupleDesc by copying from an existing
163-
* TupleDesc, but adding space for more columns. The new tupdesc is
164-
* not regarded as the same record type as the old one (and therefore
165-
* does not inherit its typeid/typmod, which instead are left as an
166-
* anonymous record type).
167-
*
168-
* The additional column slots are not initialized in any way;
169-
* callers must do their own TupleDescInitEntry on each.
170-
*
171-
* !!! Constraints and defaults are not copied !!!
172-
*/
173-
TupleDesc
174-
CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts)
175-
{
176-
TupleDesc desc;
177-
int i;
178-
int src_natts = tupdesc->natts;
179-
180-
Assert(moreatts >= 0);
181-
182-
desc = CreateTemplateTupleDesc(src_natts + moreatts, tupdesc->tdhasoid);
183-
184-
for (i = 0; i < src_natts; i++)
185-
{
186-
memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE);
187-
desc->attrs[i]->attnotnull = false;
188-
desc->attrs[i]->atthasdef = false;
189-
}
190-
191-
return desc;
192-
}
193-
194160
/*
195161
* CreateTupleDescCopyConstr
196162
* This function creates a new TupleDesc by copying from an existing
@@ -250,6 +216,47 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
250216
return desc;
251217
}
252218

219+
/*
220+
* TupleDescCopyEntry
221+
* This function copies a single attribute structure from one tuple
222+
* descriptor to another.
223+
*
224+
* !!! Constraints and defaults are not copied !!!
225+
*/
226+
void
227+
TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
228+
TupleDesc src, AttrNumber srcAttno)
229+
{
230+
/*
231+
* sanity checks
232+
*/
233+
AssertArg(PointerIsValid(src));
234+
AssertArg(PointerIsValid(dst));
235+
AssertArg(srcAttno >= 1);
236+
AssertArg(srcAttno <= src->natts);
237+
AssertArg(dstAttno >= 1);
238+
AssertArg(dstAttno <= dst->natts);
239+
240+
memcpy(dst->attrs[dstAttno - 1], src->attrs[srcAttno - 1],
241+
ATTRIBUTE_FIXED_PART_SIZE);
242+
243+
/*
244+
* Aside from updating the attno, we'd better reset attcacheoff.
245+
*
246+
* XXX Actually, to be entirely safe we'd need to reset the attcacheoff of
247+
* all following columns in dst as well. Current usage scenarios don't
248+
* require that though, because all following columns will get initialized
249+
* by other uses of this function or TupleDescInitEntry. So we cheat a
250+
* bit to avoid a useless O(N^2) penalty.
251+
*/
252+
dst->attrs[dstAttno - 1]->attnum = dstAttno;
253+
dst->attrs[dstAttno - 1]->attcacheoff = -1;
254+
255+
/* since we're not copying constraints or defaults, clear these */
256+
dst->attrs[dstAttno - 1]->attnotnull = false;
257+
dst->attrs[dstAttno - 1]->atthasdef = false;
258+
}
259+
253260
/*
254261
* Free a TupleDesc including all substructure
255262
*/

0 commit comments

Comments
 (0)