Folks,
Please find attached a patch which implements the SQL standard
UNNEST() WITH ORDINALITY. It doesn't stop there. Any function call
in a FROM clause can now take WITH ORDINALITY, which appends a counter
(ordinality) column to the columns the function outputs and produce
results like this:
postgres@postgres:5493=# 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)
TBD: polish the docs, add regression tests, possibly add psql support.
Thanks to Andrew (RhodiumToad) Gierth for the hard work designing and
implementing this feature.
Tom, is there some better way to do this?
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 12748,12753 **** postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
--- 12748,12778 ----
3
4
(4 rows)
+
+ -- unnest WITH ORDINALITY
+ postgres@postgres:5493=# 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)
</programlisting>
</para>
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 52,58 **** 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> [, ...] ] ) [ AS ]
<replaceable class="parameter">alias</replaceable> [ ( <replaceable
class="parameter">column_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> [, ...] )
<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> [, ...] ) ]
--- 52,59 ----
[ 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> [, ...] ] ) [ 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> [, ...] )
<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> [, ...] ) ]
***************
*** 366,383 **** TABLE [ ONLY ] <replaceable
class="parameter">table_name</replaceable> [ * ]
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 <command>SELECT</command> 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 <type>record</>
! data type, then an alias or the key word <literal>AS</> must
! be present, followed by a column definition list in the form
! <literal>( <replaceable
class="parameter">column_name</replaceable> <replaceable
! class="parameter">data_type</replaceable> <optional>, ... </>
! )</literal>. The column definition list must match the actual
! number and types of columns returned by the function.
</para>
</listitem>
</varlistentry>
--- 367,401 ----
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 <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 | ?column?
! --------+----------
! 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
! 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
! <type>record</> data type, then an alias or the key word
! <literal>AS</> must be present, followed by a column
! definition list in the form <literal>( <replaceable
class="parameter">column_name</replaceable> <replaceable
! class="parameter">data_type</replaceable> <optional>, ...
! </>)</literal>. The column definition list must match the
! actual number and types of columns returned by the function.
!
</para>
</listitem>
</varlistentry>
*** a/src/backend/access/common/tupdesc.c
--- b/src/backend/access/common/tupdesc.c
***************
*** 158,163 **** CreateTupleDescCopy(TupleDesc tupdesc)
--- 158,192 ----
}
/*
+ * CreateTupleDescCopyExtend
+ * This function creates a new TupleDesc by copying from an
existing
+ * TupleDesc, but adding space for more columns. The new tupdesc is
+ * not regarded as the same record type as the old one.
+ *
+ * !!! Constraints and defaults are not copied !!!
+ */
+ TupleDesc
+ CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts)
+ {
+ TupleDesc desc;
+ int i;
+ int src_natts = tupdesc->natts;
+
+ Assert(moreatts >= 0);
+
+ desc = CreateTemplateTupleDesc(src_natts + moreatts, tupdesc->tdhasoid);
+
+ for (i = 0; i < src_natts; i++)
+ {
+ memcpy(desc->attrs[i], tupdesc->attrs[i],
ATTRIBUTE_FIXED_PART_SIZE);
+ desc->attrs[i]->attnotnull = false;
+ desc->attrs[i]->atthasdef = false;
+ }
+
+ return desc;
+ }
+
+ /*
* CreateTupleDescCopyConstr
* This function creates a new TupleDesc by copying from an
existing
* TupleDesc (including its constraints and defaults).
*** a/src/backend/executor/nodeFunctionscan.c
--- b/src/backend/executor/nodeFunctionscan.c
***************
*** 25,31 ****
#include "executor/nodeFunctionscan.h"
#include "funcapi.h"
#include "nodes/nodeFuncs.h"
!
static TupleTableSlot *FunctionNext(FunctionScanState *node);
--- 25,31 ----
#include "executor/nodeFunctionscan.h"
#include "funcapi.h"
#include "nodes/nodeFuncs.h"
! #include "catalog/pg_type.h"
static TupleTableSlot *FunctionNext(FunctionScanState *node);
***************
*** 42,51 **** static TupleTableSlot *FunctionNext(FunctionScanState *node);
static TupleTableSlot *
FunctionNext(FunctionScanState *node)
{
- TupleTableSlot *slot;
EState *estate;
ScanDirection direction;
Tuplestorestate *tuplestorestate;
/*
* get information from the estate and scan state
--- 42,68 ----
static TupleTableSlot *
FunctionNext(FunctionScanState *node)
{
EState *estate;
ScanDirection direction;
Tuplestorestate *tuplestorestate;
+ TupleTableSlot *scanslot;
+ TupleTableSlot *funcslot;
+
+ if (node->func_slot)
+ {
+ /*
+ * ORDINALITY case: FUNCSLOT is the function return,
+ * SCANSLOT the scan result
+ */
+
+ funcslot = node->func_slot;
+ scanslot = node->ss.ss_ScanTupleSlot;
+ }
+ else
+ {
+ funcslot = node->ss.ss_ScanTupleSlot;
+ scanslot = NULL;
+ }
/*
* get information from the estate and scan state
***************
*** 64,82 **** FunctionNext(FunctionScanState *node)
node->tuplestorestate = tuplestorestate =
ExecMakeTableFunctionResult(node->funcexpr,
node->ss.ps.ps_ExprContext,
!
node->tupdesc,
node->eflags & EXEC_FLAG_BACKWARD);
}
/*
* Get the next tuple from tuplestore. Return NULL if no more tuples.
*/
- slot = node->ss.ss_ScanTupleSlot;
(void) tuplestore_gettupleslot(tuplestorestate,
ScanDirectionIsForward(direction),
false,
! slot);
! return slot;
}
/*
--- 81,132 ----
node->tuplestorestate = tuplestorestate =
ExecMakeTableFunctionResult(node->funcexpr,
node->ss.ps.ps_ExprContext,
!
node->func_tupdesc,
node->eflags & EXEC_FLAG_BACKWARD);
}
/*
* Get the next tuple from tuplestore. Return NULL if no more tuples.
*/
(void) tuplestore_gettupleslot(tuplestorestate,
ScanDirectionIsForward(direction),
false,
! funcslot);
!
! if (!scanslot)
! return funcslot;
!
! /*
! * we're doing ordinality, so we copy the values from the function
return
! * slot to the (distinct) scan slot. We can do this because the
lifetimes
! * of the values in each slot are the same; until we reset the scan or
! * fetch the next tuple, both will be valid.
! */
!
! ExecClearTuple(scanslot);
!
! if (!TupIsNull(funcslot))
! {
! int natts = funcslot->tts_tupleDescriptor->natts;
! int i;
!
! slot_getallattrs(funcslot);
!
! for (i = 0; i < natts; ++i)
! {
! scanslot->tts_values[i] = funcslot->tts_values[i];
! scanslot->tts_isnull[i] = funcslot->tts_isnull[i];
! }
!
! node->ordinal++;
!
! scanslot->tts_values[natts] = Int64GetDatumFast(node->ordinal);
! scanslot->tts_isnull[natts] = false;
!
! ExecStoreVirtualTuple(scanslot);
! }
!
! return scanslot;
}
/*
***************
*** 116,122 **** ExecInitFunctionScan(FunctionScan *node, EState *estate, int
eflags)
FunctionScanState *scanstate;
Oid funcrettype;
TypeFuncClass functypclass;
! TupleDesc tupdesc = NULL;
/* check for unsupported flags */
Assert(!(eflags & EXEC_FLAG_MARK));
--- 166,173 ----
FunctionScanState *scanstate;
Oid funcrettype;
TypeFuncClass functypclass;
! TupleDesc func_tupdesc = NULL;
! TupleDesc scan_tupdesc = NULL;
/* check for unsupported flags */
Assert(!(eflags & EXEC_FLAG_MARK));
***************
*** 148,153 **** ExecInitFunctionScan(FunctionScan *node, EState *estate, int
eflags)
--- 199,209 ----
ExecInitResultTupleSlot(estate, &scanstate->ss.ps);
ExecInitScanTupleSlot(estate, &scanstate->ss);
+ if (node->funcordinality)
+ scanstate->func_slot = ExecInitExtraTupleSlot(estate);
+ else
+ scanstate->func_slot = NULL;
+
/*
* initialize child expressions
*/
***************
*** 164,200 **** ExecInitFunctionScan(FunctionScan *node, EState *estate, int
eflags)
*/
functypclass = get_expr_result_type(node->funcexpr,
&funcrettype,
!
&tupdesc);
if (functypclass == TYPEFUNC_COMPOSITE)
{
/* Composite data type, e.g. a table's row type */
! Assert(tupdesc);
/* Must copy it out of typcache for safety */
! tupdesc = CreateTupleDescCopy(tupdesc);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
char *attname = strVal(linitial(node->funccolnames));
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc,
(AttrNumber) 1,
attname,
funcrettype,
-1,
0);
! TupleDescInitEntryCollation(tupdesc,
(AttrNumber) 1,
exprCollation(node->funcexpr));
}
else if (functypclass == TYPEFUNC_RECORD)
{
! tupdesc = BuildDescFromLists(node->funccolnames,
!
node->funccoltypes,
!
node->funccoltypmods,
!
node->funccolcollations);
}
else
{
--- 220,267 ----
*/
functypclass = get_expr_result_type(node->funcexpr,
&funcrettype,
!
&func_tupdesc);
if (functypclass == TYPEFUNC_COMPOSITE)
{
/* Composite data type, e.g. a table's row type */
! Assert(func_tupdesc);
!
! /*
! * XXX
! * Existing behaviour is a bit inconsistent with regard to
aliases and
! * whole-row Vars of the function result. If the function
returns a
! * composite type, then the whole-row Var will refer to this
tupdesc,
! * which has the type's own column names rather than the alias
column
! * names given in the query. This affects the output of
constructs like
! * row_to_json which read the column names from the passed-in
values.
! */
!
/* Must copy it out of typcache for safety */
! func_tupdesc = CreateTupleDescCopy(func_tupdesc);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
char *attname = strVal(linitial(node->funccolnames));
! func_tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(func_tupdesc,
(AttrNumber) 1,
attname,
funcrettype,
-1,
0);
! TupleDescInitEntryCollation(func_tupdesc,
(AttrNumber) 1,
exprCollation(node->funcexpr));
}
else if (functypclass == TYPEFUNC_RECORD)
{
! func_tupdesc = BuildDescFromLists(node->funccolnames,
!
node->funccoltypes,
!
node->funccoltypmods,
!
node->funccolcollations);
}
else
{
***************
*** 207,220 **** ExecInitFunctionScan(FunctionScan *node, EState *estate, int
eflags)
* function should do this for itself, but let's cover things in case it
* doesn't.)
*/
! BlessTupleDesc(tupdesc);
! scanstate->tupdesc = tupdesc;
! ExecAssignScanType(&scanstate->ss, tupdesc);
/*
* Other node-specific setup
*/
scanstate->tuplestorestate = NULL;
scanstate->funcexpr = ExecInitExpr((Expr *) node->funcexpr,
(PlanState *) scanstate);
--- 274,309 ----
* function should do this for itself, but let's cover things in case it
* doesn't.)
*/
! BlessTupleDesc(func_tupdesc);
! if (node->funcordinality)
! {
! int natts = func_tupdesc->natts;
!
! scan_tupdesc = CreateTupleDescCopyExtend(func_tupdesc, 1);
!
! TupleDescInitEntry(scan_tupdesc,
! natts + 1,
!
strVal(llast(node->funccolnames)),
! INT8OID,
! -1,
! 0);
!
! BlessTupleDesc(scan_tupdesc);
!
! ExecSetSlotDescriptor(scanstate->func_slot, func_tupdesc);
! }
! else
! scan_tupdesc = func_tupdesc;
!
! scanstate->scan_tupdesc = scan_tupdesc;
! scanstate->func_tupdesc = func_tupdesc;
! ExecAssignScanType(&scanstate->ss, scan_tupdesc);
/*
* Other node-specific setup
*/
+ scanstate->ordinal = 0;
scanstate->tuplestorestate = NULL;
scanstate->funcexpr = ExecInitExpr((Expr *) node->funcexpr,
(PlanState *) scanstate);
***************
*** 249,254 **** ExecEndFunctionScan(FunctionScanState *node)
--- 338,345 ----
*/
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
ExecClearTuple(node->ss.ss_ScanTupleSlot);
+ if (node->func_slot)
+ ExecClearTuple(node->func_slot);
/*
* Release tuplestore resources
***************
*** 268,276 **** void
--- 359,371 ----
ExecReScanFunctionScan(FunctionScanState *node)
{
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
+ if (node->func_slot)
+ ExecClearTuple(node->func_slot);
ExecScanReScan(&node->ss);
+ node->ordinal = 0;
+
/*
* If we haven't materialized yet, just return.
*/
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 503,508 **** _copyFunctionScan(const FunctionScan *from)
--- 503,509 ----
* copy remainder of node
*/
COPY_NODE_FIELD(funcexpr);
+ COPY_SCALAR_FIELD(funcordinality);
COPY_NODE_FIELD(funccolnames);
COPY_NODE_FIELD(funccoltypes);
COPY_NODE_FIELD(funccoltypmods);
***************
*** 1989,1994 **** _copyRangeTblEntry(const RangeTblEntry *from)
--- 1990,1996 ----
COPY_NODE_FIELD(alias);
COPY_NODE_FIELD(eref);
COPY_SCALAR_FIELD(lateral);
+ COPY_SCALAR_FIELD(ordinality);
COPY_SCALAR_FIELD(inh);
COPY_SCALAR_FIELD(inFromCl);
COPY_SCALAR_FIELD(requiredPerms);
***************
*** 2279,2284 **** _copyRangeFunction(const RangeFunction *from)
--- 2281,2287 ----
RangeFunction *newnode = makeNode(RangeFunction);
COPY_SCALAR_FIELD(lateral);
+ COPY_SCALAR_FIELD(ordinality);
COPY_NODE_FIELD(funccallnode);
COPY_NODE_FIELD(alias);
COPY_NODE_FIELD(coldeflist);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 2123,2128 **** static bool
--- 2123,2129 ----
_equalRangeFunction(const RangeFunction *a, const RangeFunction *b)
{
COMPARE_SCALAR_FIELD(lateral);
+ COMPARE_SCALAR_FIELD(ordinality);
COMPARE_NODE_FIELD(funccallnode);
COMPARE_NODE_FIELD(alias);
COMPARE_NODE_FIELD(coldeflist);
***************
*** 2241,2246 **** _equalRangeTblEntry(const RangeTblEntry *a, const
RangeTblEntry *b)
--- 2242,2248 ----
COMPARE_NODE_FIELD(alias);
COMPARE_NODE_FIELD(eref);
COMPARE_SCALAR_FIELD(lateral);
+ COMPARE_SCALAR_FIELD(ordinality);
COMPARE_SCALAR_FIELD(inh);
COMPARE_SCALAR_FIELD(inFromCl);
COMPARE_SCALAR_FIELD(requiredPerms);
*** a/src/backend/nodes/makefuncs.c
--- b/src/backend/nodes/makefuncs.c
***************
*** 153,164 **** makeWholeRowVar(RangeTblEntry *rte,
break;
case RTE_FUNCTION:
toid = exprType(rte->funcexpr);
! if (type_is_rowtype(toid))
{
/* func returns composite; same as relation
case */
result = makeVar(varno,
InvalidAttrNumber,
! toid,
-1,
InvalidOid,
varlevelsup);
--- 153,164 ----
break;
case RTE_FUNCTION:
toid = exprType(rte->funcexpr);
! if (type_is_rowtype(toid) || rte->ordinality)
{
/* func returns composite; same as relation
case */
result = makeVar(varno,
InvalidAttrNumber,
!
(rte->ordinality) ? RECORDOID : toid,
-1,
InvalidOid,
varlevelsup);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 515,520 **** _outFunctionScan(StringInfo str, const FunctionScan *node)
--- 515,521 ----
_outScanInfo(str, (const Scan *) node);
WRITE_NODE_FIELD(funcexpr);
+ WRITE_BOOL_FIELD(funcordinality);
WRITE_NODE_FIELD(funccolnames);
WRITE_NODE_FIELD(funccoltypes);
WRITE_NODE_FIELD(funccoltypmods);
***************
*** 2384,2389 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
--- 2385,2391 ----
}
WRITE_BOOL_FIELD(lateral);
+ WRITE_BOOL_FIELD(ordinality);
WRITE_BOOL_FIELD(inh);
WRITE_BOOL_FIELD(inFromCl);
WRITE_UINT_FIELD(requiredPerms);
***************
*** 2598,2603 **** _outRangeFunction(StringInfo str, const RangeFunction *node)
--- 2600,2606 ----
WRITE_NODE_TYPE("RANGEFUNCTION");
WRITE_BOOL_FIELD(lateral);
+ WRITE_BOOL_FIELD(ordinality);
WRITE_NODE_FIELD(funccallnode);
WRITE_NODE_FIELD(alias);
WRITE_NODE_FIELD(coldeflist);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1224,1229 **** _readRangeTblEntry(void)
--- 1224,1230 ----
}
READ_BOOL_FIELD(lateral);
+ READ_BOOL_FIELD(ordinality);
READ_BOOL_FIELD(inh);
READ_BOOL_FIELD(inFromCl);
READ_UINT_FIELD(requiredPerms);
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 114,121 **** static BitmapHeapScan *make_bitmap_heapscan(List *qptlist,
static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
List *tidquals);
static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
! Index scanrelid, Node *funcexpr, List
*funccolnames,
! List *funccoltypes, List *funccoltypmods,
List *funccolcollations);
static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
Index scanrelid, List *values_lists);
--- 114,121 ----
static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
List *tidquals);
static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
! Index scanrelid, Node *funcexpr, bool
ordinality,
! List *funccolnames, List *funccoltypes, List
*funccoltypmods,
List *funccolcollations);
static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
Index scanrelid, List *values_lists);
***************
*** 1723,1728 **** create_functionscan_plan(PlannerInfo *root, Path *best_path,
--- 1723,1729 ----
scan_plan = make_functionscan(tlist, scan_clauses, scan_relid,
funcexpr,
+
rte->ordinality,
rte->eref->colnames,
rte->funccoltypes,
rte->funccoltypmods,
***************
*** 3356,3361 **** make_functionscan(List *qptlist,
--- 3357,3363 ----
List *qpqual,
Index scanrelid,
Node *funcexpr,
+ bool ordinality,
List *funccolnames,
List *funccoltypes,
List *funccoltypmods,
***************
*** 3371,3376 **** make_functionscan(List *qptlist,
--- 3373,3379 ----
plan->righttree = NULL;
node->scan.scanrelid = scanrelid;
node->funcexpr = funcexpr;
+ node->funcordinality = ordinality;
node->funccolnames = funccolnames;
node->funccoltypes = funccoltypes;
node->funccoltypmods = funccoltypmods;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 562,568 **** static void processCASbits(int cas_bits, int location, const
char *constrType,
NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
! ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION
PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
--- 562,568 ----
NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
! ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION
PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
***************
*** 591,597 **** static void processCASbits(int cas_bits, int location, const
char *constrType,
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WINDOW WITH WITHOUT WORK WRAPPER WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
--- 591,597 ----
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
VERBOSE VERSION_P VIEW VOLATILE
! WHEN WHERE WHITESPACE_P WINDOW WITH WITH_ORDINALITY WITHOUT WORK
WRAPPER WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLPARSE
XMLPI XMLROOT XMLSERIALIZE
***************
*** 9391,9410 **** table_ref: relation_expr opt_alias_clause
--- 9391,9432 ----
{
RangeFunction *n =
makeNode(RangeFunction);
n->lateral = false;
+ n->ordinality = false;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
$$ = (Node *) n;
}
+ | func_table WITH_ORDINALITY func_alias_clause
+ {
+ RangeFunction *n =
makeNode(RangeFunction);
+ n->lateral = false;
+ n->ordinality = true;
+ n->funccallnode = $1;
+ n->alias = linitial($3);
+ n->coldeflist = lsecond($3);
+ $$ = (Node *) n;
+ }
| LATERAL_P func_table func_alias_clause
{
RangeFunction *n =
makeNode(RangeFunction);
n->lateral = true;
+ n->ordinality = false;
n->funccallnode = $2;
n->alias = linitial($3);
n->coldeflist = lsecond($3);
$$ = (Node *) n;
}
+ | LATERAL_P func_table WITH_ORDINALITY func_alias_clause
+ {
+ RangeFunction *n =
makeNode(RangeFunction);
+ n->lateral = true;
+ n->ordinality = true;
+ n->funccallnode = $2;
+ n->alias = linitial($4);
+ n->coldeflist = lsecond($4);
+ $$ = (Node *) n;
+ }
| select_with_parens opt_alias_clause
{
RangeSubselect *n =
makeNode(RangeSubselect);
***************
*** 12583,12588 **** unreserved_keyword:
--- 12605,12611 ----
| OPERATOR
| OPTION
| OPTIONS
+ | ORDINALITY
| OWNED
| OWNER
| PARSER
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
***************
*** 536,543 **** transformRangeFunction(ParseState *pstate, RangeFunction *r)
/*
* OK, build an RTE for the function.
*/
! rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
!
r, r->lateral, true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
--- 536,542 ----
/*
* OK, build an RTE for the function.
*/
! rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr, r,
true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
*** a/src/backend/parser/parse_relation.c
--- b/src/backend/parser/parse_relation.c
***************
*** 796,802 **** markVarForSelectPriv(ParseState *pstate, Var *var,
RangeTblEntry *rte)
* physical column numbers.
*/
static void
! buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref)
{
int maxattrs = tupdesc->natts;
ListCell *aliaslc;
--- 796,802 ----
* physical column numbers.
*/
static void
! buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref, bool
ordinality)
{
int maxattrs = tupdesc->natts;
ListCell *aliaslc;
***************
*** 848,853 **** buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias
*eref)
--- 848,872 ----
eref->colnames = lappend(eref->colnames, attrname);
}
+ /* tack on the ordinality column at the end */
+ if (ordinality)
+ {
+ Value *attrname;
+
+ if (aliaslc)
+ {
+ attrname = (Value *) lfirst(aliaslc);
+ aliaslc = lnext(aliaslc);
+ alias->colnames = lappend(alias->colnames, attrname);
+ }
+ else
+ {
+ attrname = makeString(pstrdup("?column?"));
+ }
+
+ eref->colnames = lappend(eref->colnames, attrname);
+ }
+
/* Too many user-supplied aliases? */
if (aliaslc)
ereport(ERROR,
***************
*** 870,912 **** buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias
*eref)
*/
static void
buildScalarFunctionAlias(Node *funcexpr, char *funcname,
! Alias *alias, Alias *eref)
{
- char *pname;
-
Assert(eref->colnames == NIL);
/* Use user-specified column alias if there is one. */
if (alias && alias->colnames != NIL)
{
! if (list_length(alias->colnames) != 1)
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("too many column aliases specified for
function %s",
funcname)));
eref->colnames = copyObject(alias->colnames);
- return;
}
!
! /*
! * If the expression is a simple function call, and the function has a
! * single OUT parameter that is named, use the parameter's name.
! */
! if (funcexpr && IsA(funcexpr, FuncExpr))
{
! pname = get_func_result_name(((FuncExpr *) funcexpr)->funcid);
! if (pname)
! {
! eref->colnames = list_make1(makeString(pname));
! return;
! }
}
! /*
! * Otherwise use the previously-determined alias (not necessarily the
! * function name!)
! */
! eref->colnames = list_make1(makeString(eref->aliasname));
}
/*
--- 889,930 ----
*/
static void
buildScalarFunctionAlias(Node *funcexpr, char *funcname,
! Alias *alias, Alias *eref,
bool ordinality)
{
Assert(eref->colnames == NIL);
/* Use user-specified column alias if there is one. */
if (alias && alias->colnames != NIL)
{
! if (list_length(alias->colnames) > (ordinality ? 2 : 1))
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("too many column aliases specified for
function %s",
funcname)));
+
eref->colnames = copyObject(alias->colnames);
}
! else
{
! char *pname = NULL;
!
! /*
! * If the expression is a simple function call, and the
function has a
! * single OUT parameter that is named, use the parameter's name.
! */
! if (funcexpr && IsA(funcexpr, FuncExpr))
! pname = get_func_result_name(((FuncExpr *)
funcexpr)->funcid);
!
! if (!pname)
! pname = eref->aliasname;
!
! eref->colnames = list_make1(makeString(pname));
}
! if (ordinality && list_length(eref->colnames) < 2)
! eref->colnames = lappend(eref->colnames,
makeString(pstrdup("?column?")));
!
! return;
}
/*
***************
*** 1002,1008 **** addRangeTableEntry(ParseState *pstate,
* and/or actual column names.
*/
rte->eref = makeAlias(refname, NIL);
! buildRelationAliases(rel->rd_att, alias, rte->eref);
/*
* Drop the rel refcount, but keep the access lock till end of
transaction
--- 1020,1026 ----
* and/or actual column names.
*/
rte->eref = makeAlias(refname, NIL);
! buildRelationAliases(rel->rd_att, alias, rte->eref, false);
/*
* Drop the rel refcount, but keep the access lock till end of
transaction
***************
*** 1062,1068 **** addRangeTableEntryForRelation(ParseState *pstate,
* and/or actual column names.
*/
rte->eref = makeAlias(refname, NIL);
! buildRelationAliases(rel->rd_att, alias, rte->eref);
/*
* Set flags and access permissions.
--- 1080,1086 ----
* and/or actual column names.
*/
rte->eref = makeAlias(refname, NIL);
! buildRelationAliases(rel->rd_att, alias, rte->eref, false);
/*
* Set flags and access permissions.
***************
*** 1177,1183 **** addRangeTableEntryForFunction(ParseState *pstate,
char *funcname,
Node *funcexpr,
RangeFunction
*rangefunc,
- bool lateral,
bool inFromCl)
{
RangeTblEntry *rte = makeNode(RangeTblEntry);
--- 1195,1200 ----
***************
*** 1233,1249 **** addRangeTableEntryForFunction(ParseState *pstate,
/* Composite data type, e.g. a table's row type */
Assert(tupdesc);
/* Build the column alias list */
! buildRelationAliases(tupdesc, alias, eref);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
! buildScalarFunctionAlias(funcexpr, funcname, alias, eref);
}
else if (functypclass == TYPEFUNC_RECORD)
{
ListCell *col;
/*
* Use the column definition list to form the alias list and
* funccoltypes/funccoltypmods/funccolcollations lists.
--- 1250,1272 ----
/* Composite data type, e.g. a table's row type */
Assert(tupdesc);
/* Build the column alias list */
! buildRelationAliases(tupdesc, alias, eref,
rangefunc->ordinality);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
! buildScalarFunctionAlias(funcexpr, funcname, alias, eref,
rangefunc->ordinality);
}
else if (functypclass == TYPEFUNC_RECORD)
{
ListCell *col;
+ if (rangefunc->ordinality)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WITH ORDINALITY is not
supported for functions returning \"record\""),
+ parser_errposition(pstate,
exprLocation(funcexpr))));
+
/*
* Use the column definition list to form the alias list and
* funccoltypes/funccoltypmods/funccolcollations lists.
***************
*** 1285,1291 **** addRangeTableEntryForFunction(ParseState *pstate,
* Functions are never checked for access rights (at least, not by the
RTE
* permissions mechanism).
*/
! rte->lateral = lateral;
rte->inh = false; /* never true for functions */
rte->inFromCl = inFromCl;
--- 1308,1315 ----
* Functions are never checked for access rights (at least, not by the
RTE
* permissions mechanism).
*/
! rte->lateral = rangefunc->lateral;
! rte->ordinality = rangefunc->ordinality;
rte->inh = false; /* never true for functions */
rte->inFromCl = inFromCl;
***************
*** 1709,1714 **** expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
--- 1733,1739 ----
TypeFuncClass functypclass;
Oid funcrettype;
TupleDesc tupdesc;
+ int ordattno = 0;
functypclass =
get_expr_result_type(rte->funcexpr,
&funcrettype,
***************
*** 1720,1725 **** expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
--- 1745,1751 ----
expandTupleDesc(tupdesc, rte->eref,
rtindex, sublevels_up, location,
include_dropped, colnames, colvars);
+ ordattno = tupdesc->natts + 1;
}
else if (functypclass == TYPEFUNC_SCALAR)
{
***************
*** 1740,1745 **** expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
--- 1766,1773 ----
*colvars = lappend(*colvars,
varnode);
}
+
+ ordattno = 2;
}
else if (functypclass == TYPEFUNC_RECORD)
{
***************
*** 1778,1783 **** expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
--- 1806,1829 ----
/* addRangeTableEntryForFunction
should've caught this */
elog(ERROR, "function in FROM has
unsupported return type");
}
+
+ /* tack on the extra ordinality column if
present */
+ if (rte->ordinality)
+ {
+ if (colnames)
+ *colnames = lappend(*colnames,
llast(rte->eref->colnames));
+
+ if (colvars)
+ {
+ Var *varnode = makeVar(rtindex,
+
ordattno,
+
INT8OID,
+
-1,
+
InvalidOid,
+
sublevels_up);
+ *colvars = lappend(*colvars,
varnode);
+ }
+ }
}
break;
case RTE_VALUES:
*** a/src/backend/parser/parser.c
--- b/src/backend/parser/parser.c
***************
*** 133,139 **** base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t
yyscanner)
case WITH:
/*
! * WITH TIME must be reduced to one token
*/
cur_yylval = lvalp->core_yystype;
cur_yylloc = *llocp;
--- 133,139 ----
case WITH:
/*
! * WITH TIME and WITH ORDINALITY must each be reduced
to one token
*/
cur_yylval = lvalp->core_yystype;
cur_yylloc = *llocp;
***************
*** 143,148 **** base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t
yyscanner)
--- 143,151 ----
case TIME:
cur_token = WITH_TIME;
break;
+ case ORDINALITY:
+ cur_token = WITH_ORDINALITY;
+ break;
default:
/* save the lookahead token for next
time */
yyextra->lookahead_token = next_token;
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 7938,7943 **** get_from_clause_item(Node *jtnode, Query *query,
deparse_context *context)
--- 7938,7945 ----
case RTE_FUNCTION:
/* Function RTE */
get_rule_expr(rte->funcexpr, context, true);
+ if (rte->ordinality)
+ appendStringInfoString(buf, " WITH
ORDINALITY");
break;
case RTE_VALUES:
/* Values list RTE */
*** a/src/include/access/tupdesc.h
--- b/src/include/access/tupdesc.h
***************
*** 87,92 **** extern TupleDesc CreateTupleDesc(int natts, bool hasoid,
--- 87,93 ----
Form_pg_attribute *attrs);
extern TupleDesc CreateTupleDescCopy(TupleDesc tupdesc);
+ extern TupleDesc CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts);
extern TupleDesc CreateTupleDescCopyConstr(TupleDesc tupdesc);
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 1393,1399 **** typedef struct FunctionScanState
{
ScanState ss; /* its first field is
NodeTag */
int eflags;
! TupleDesc tupdesc;
Tuplestorestate *tuplestorestate;
ExprState *funcexpr;
} FunctionScanState;
--- 1393,1402 ----
{
ScanState ss; /* its first field is
NodeTag */
int eflags;
! int64 ordinal;
! TupleDesc scan_tupdesc;
! TupleDesc func_tupdesc;
! TupleTableSlot *func_slot;
Tuplestorestate *tuplestorestate;
ExprState *funcexpr;
} FunctionScanState;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 463,468 **** typedef struct RangeFunction
--- 463,469 ----
{
NodeTag type;
bool lateral; /* does it have LATERAL prefix?
*/
+ bool ordinality; /* does it have WITH ORDINALITY
suffix? */
Node *funccallnode; /* untransformed function call tree */
Alias *alias; /* table alias & optional
column aliases */
List *coldeflist; /* list of ColumnDef nodes to describe
result
***************
*** 759,764 **** typedef struct RangeTblEntry
--- 760,766 ----
Alias *alias; /* user-written alias clause,
if any */
Alias *eref; /* expanded reference names */
bool lateral; /* subquery, function, or
values is LATERAL? */
+ bool ordinality; /* function WITH ORDINALITY? */
bool inh; /* inheritance requested? */
bool inFromCl; /* present in FROM clause? */
AclMode requiredPerms; /* bitmask of required access
permissions */
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 423,428 **** typedef struct FunctionScan
--- 423,429 ----
{
Scan scan;
Node *funcexpr; /* expression tree for func call */
+ bool funcordinality;
List *funccolnames; /* output column names (string Value
nodes) */
List *funccoltypes; /* OID list of column type OIDs */
List *funccoltypmods; /* integer list of column typmods */
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 267,272 **** PG_KEYWORD("option", OPTION, UNRESERVED_KEYWORD)
--- 267,273 ----
PG_KEYWORD("options", OPTIONS, UNRESERVED_KEYWORD)
PG_KEYWORD("or", OR, RESERVED_KEYWORD)
PG_KEYWORD("order", ORDER, RESERVED_KEYWORD)
+ PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD)
PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD)
PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("over", OVER, TYPE_FUNC_NAME_KEYWORD)
*** a/src/include/parser/parse_relation.h
--- b/src/include/parser/parse_relation.h
***************
*** 61,67 **** extern RangeTblEntry *addRangeTableEntryForFunction(ParseState
*pstate,
char *funcname,
Node *funcexpr,
RangeFunction
*rangefunc,
- bool lateral,
bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForValues(ParseState *pstate,
List *exprs,
--- 61,66 ----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
