anonymous composite types for Table Functions (aka SRFs) - Mailing list pgsql-patches
| From | Joe Conway |
|---|---|
| Subject | anonymous composite types for Table Functions (aka SRFs) |
| Date | |
| Msg-id | 3D44D192.2030407@joeconway.com Whole thread Raw |
| Responses |
Re: anonymous composite types for Table Functions (aka SRFs)
Re: anonymous composite types for Table Functions (aka SRFs) Re: anonymous composite types for Table Functions (aka SRFs) Re: anonymous composite types for Table Functions (aka SRFs) |
| List | pgsql-patches |
Attached are two patches to implement and document anonymous composite
types for Table Functions, as previously proposed on HACKERS. Here is a
brief explanation:
1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
'b' for base or 'c' for catalog, i.e. a class).
2. Creates new builtin type of typtype='p' named RECORD. This is the
first of potentially several pseudo types.
3. Modify FROM clause grammer to accept:
SELECT * FROM my_func() AS m(colname1 type1, colname2 type1, ...)
where m is the table alias, colname1, etc are the column names, and
type1, etc are the column types.
4. When typtype == 'p' and the function return type is RECORD, a list
of column defs is required, and when typtype != 'p', it is disallowed.
5. A check was added to ensure that the tupdesc provide via the parser
and the actual return tupdesc match in number and type of attributes.
When creating a function you can do:
CREATE FUNCTION foo(text) RETURNS setof RECORD ...
When using it you can do:
SELECT * from foo(sqlstmt) AS (f1 int, f2 text, f3 timestamp)
or
SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)
or
SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)
Included in the patches are adjustments to the regression test sql and
expected files, and documentation.
If there are no objections, please apply.
Thanks,
Joe
p.s.
This potentially solves (or at least improves) the issue of builtin
Table Functions. They can be bootstrapped as returning RECORD, and
we can wrap system views around them with properly specified column
defs. For example:
CREATE VIEW pg_settings AS
SELECT s.name, s.setting
FROM show_all_settings()AS s(name text, setting text);
Then we can also add the UPDATE RULE that I previously posted to
pg_settings, and have pg_settings act like a virtual table, allowing
settings to be queried and set.
Index: src/backend/access/common/tupdesc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/access/common/tupdesc.c,v
retrieving revision 1.81
diff -c -r1.81 tupdesc.c
*** src/backend/access/common/tupdesc.c 20 Jul 2002 05:16:56 -0000 1.81
--- src/backend/access/common/tupdesc.c 28 Jul 2002 01:33:30 -0000
***************
*** 24,29 ****
--- 24,30 ----
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "nodes/parsenodes.h"
+ #include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "utils/builtins.h"
#include "utils/syscache.h"
***************
*** 597,642 ****
TupleDesc
TypeGetTupleDesc(Oid typeoid, List *colaliases)
{
! Oid relid = typeidTypeRelid(typeoid);
! TupleDesc tupdesc;
/*
* Build a suitable tupledesc representing the output rows
*/
! if (OidIsValid(relid))
{
/* Composite data type, i.e. a table's row type */
! Relation rel;
! int natts;
!
! rel = relation_open(relid, AccessShareLock);
! tupdesc = CreateTupleDescCopy(RelationGetDescr(rel));
! natts = tupdesc->natts;
! relation_close(rel, AccessShareLock);
! /* check to see if we've given column aliases */
! if(colaliases != NIL)
{
! char *label;
! int varattno;
! /* does the List length match the number of attributes */
! if (length(colaliases) != natts)
! elog(ERROR, "TypeGetTupleDesc: number of aliases does not match number of attributes");
! /* OK, use the aliases instead */
! for (varattno = 0; varattno < natts; varattno++)
{
! label = strVal(nth(varattno, colaliases));
! if (label != NULL)
! namestrcpy(&(tupdesc->attrs[varattno]->attname), label);
! else
! MemSet(NameStr(tupdesc->attrs[varattno]->attname), 0, NAMEDATALEN);
}
}
}
! else
{
/* Must be a base data type, i.e. scalar */
char *attname;
--- 598,650 ----
TupleDesc
TypeGetTupleDesc(Oid typeoid, List *colaliases)
{
! char functyptype = typeid_get_typtype(typeoid);
! TupleDesc tupdesc = NULL;
/*
* Build a suitable tupledesc representing the output rows
*/
! if (functyptype == 'c')
{
/* Composite data type, i.e. a table's row type */
! Oid relid = typeidTypeRelid(typeoid);
! if (OidIsValid(relid))
{
! Relation rel;
! int natts;
! rel = relation_open(relid, AccessShareLock);
! tupdesc = CreateTupleDescCopy(RelationGetDescr(rel));
! natts = tupdesc->natts;
! relation_close(rel, AccessShareLock);
! /* check to see if we've given column aliases */
! if(colaliases != NIL)
{
! char *label;
! int varattno;
! /* does the List length match the number of attributes */
! if (length(colaliases) != natts)
! elog(ERROR, "TypeGetTupleDesc: number of aliases does not match number of attributes");
!
! /* OK, use the aliases instead */
! for (varattno = 0; varattno < natts; varattno++)
! {
! label = strVal(nth(varattno, colaliases));
!
! if (label != NULL)
! namestrcpy(&(tupdesc->attrs[varattno]->attname), label);
! else
! MemSet(NameStr(tupdesc->attrs[varattno]->attname), 0, NAMEDATALEN);
! }
}
}
+ else
+ elog(ERROR, "Invalid return relation specified for function");
}
! else if (functyptype == 'b')
{
/* Must be a base data type, i.e. scalar */
char *attname;
***************
*** 661,666 ****
--- 669,679 ----
0,
false);
}
+ else if (functyptype == 'p' && typeoid == RECORDOID)
+ elog(ERROR, "Unable to determine tuple description for function"
+ " returning \"record\"");
+ else
+ elog(ERROR, "Unknown kind of return type specified for function");
return tupdesc;
}
Index: src/backend/catalog/pg_proc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/catalog/pg_proc.c,v
retrieving revision 1.81
diff -c -r1.81 pg_proc.c
*** src/backend/catalog/pg_proc.c 24 Jul 2002 19:11:09 -0000 1.81
--- src/backend/catalog/pg_proc.c 29 Jul 2002 02:02:31 -0000
***************
*** 25,30 ****
--- 25,31 ----
#include "miscadmin.h"
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
+ #include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
***************
*** 33,39 ****
#include "utils/syscache.h"
! static void checkretval(Oid rettype, List *queryTreeList);
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
Datum fmgr_sql_validator(PG_FUNCTION_ARGS);
--- 34,40 ----
#include "utils/syscache.h"
! static void checkretval(Oid rettype, char fn_typtype, List *queryTreeList);
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
Datum fmgr_sql_validator(PG_FUNCTION_ARGS);
***************
*** 317,323 ****
* type he claims.
*/
static void
! checkretval(Oid rettype, List *queryTreeList)
{
Query *parse;
int cmd;
--- 318,324 ----
* type he claims.
*/
static void
! checkretval(Oid rettype, char fn_typtype, List *queryTreeList)
{
Query *parse;
int cmd;
***************
*** 367,447 ****
*/
tlistlen = ExecCleanTargetListLength(tlist);
- /*
- * For base-type returns, the target list should have exactly one
- * entry, and its type should agree with what the user declared. (As
- * of Postgres 7.2, we accept binary-compatible types too.)
- */
typerelid = typeidTypeRelid(rettype);
- if (typerelid == InvalidOid)
- {
- if (tlistlen != 1)
- elog(ERROR, "function declared to return %s returns multiple columns in final SELECT",
- format_type_be(rettype));
! restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
! if (!IsBinaryCompatible(restype, rettype))
! elog(ERROR, "return type mismatch in function: declared to return %s, returns %s",
! format_type_be(rettype), format_type_be(restype));
! return;
! }
- /*
- * If the target list is of length 1, and the type of the varnode in
- * the target list matches the declared return type, this is okay.
- * This can happen, for example, where the body of the function is
- * 'SELECT func2()', where func2 has the same return type as the
- * function that's calling it.
- */
- if (tlistlen == 1)
- {
- restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
- if (IsBinaryCompatible(restype, rettype))
return;
}
! /*
! * By here, the procedure returns a tuple or set of tuples. This part
! * of the typechecking is a hack. We look up the relation that is the
! * declared return type, and be sure that attributes 1 .. n in the
! * target list match the declared types.
! */
! reln = heap_open(typerelid, AccessShareLock);
! relid = reln->rd_id;
! relnatts = reln->rd_rel->relnatts;
!
! if (tlistlen != relnatts)
! elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
! format_type_be(rettype), relnatts);
! /* expect attributes 1 .. n in order */
! i = 0;
! foreach(tlistitem, tlist)
! {
! TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
! Oid tletype;
! Oid atttype;
!
! if (tle->resdom->resjunk)
! continue;
! tletype = exprType(tle->expr);
! atttype = reln->rd_att->attrs[i]->atttypid;
! if (!IsBinaryCompatible(tletype, atttype))
! elog(ERROR, "function declared to return %s returns %s instead of %s at column %d",
! format_type_be(rettype),
! format_type_be(tletype),
! format_type_be(atttype),
! i + 1);
! i++;
! }
!
! /* this shouldn't happen, but let's just check... */
! if (i != relnatts)
! elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
! format_type_be(rettype), relnatts);
! heap_close(reln, AccessShareLock);
}
--- 368,467 ----
*/
tlistlen = ExecCleanTargetListLength(tlist);
typerelid = typeidTypeRelid(rettype);
! if (fn_typtype == 'b')
! {
! /*
! * For base-type returns, the target list should have exactly one
! * entry, and its type should agree with what the user declared. (As
! * of Postgres 7.2, we accept binary-compatible types too.)
! */
! if (typerelid == InvalidOid)
! {
! if (tlistlen != 1)
! elog(ERROR, "function declared to return %s returns multiple columns in final SELECT",
! format_type_be(rettype));
!
! restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
! if (!IsBinaryCompatible(restype, rettype))
! elog(ERROR, "return type mismatch in function: declared to return %s, returns %s",
! format_type_be(rettype), format_type_be(restype));
return;
+ }
+
+ /*
+ * If the target list is of length 1, and the type of the varnode in
+ * the target list matches the declared return type, this is okay.
+ * This can happen, for example, where the body of the function is
+ * 'SELECT func2()', where func2 has the same return type as the
+ * function that's calling it.
+ */
+ if (tlistlen == 1)
+ {
+ restype = ((TargetEntry *) lfirst(tlist))->resdom->restype;
+ if (IsBinaryCompatible(restype, rettype))
+ return;
+ }
}
+ else if (fn_typtype == 'c')
+ {
+ /*
+ * By here, the procedure returns a tuple or set of tuples. This part
+ * of the typechecking is a hack. We look up the relation that is the
+ * declared return type, and be sure that attributes 1 .. n in the
+ * target list match the declared types.
+ */
+ reln = heap_open(typerelid, AccessShareLock);
+ relid = reln->rd_id;
+ relnatts = reln->rd_rel->relnatts;
+
+ if (tlistlen != relnatts)
+ elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
+ format_type_be(rettype), relnatts);
+
+ /* expect attributes 1 .. n in order */
+ i = 0;
+ foreach(tlistitem, tlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
+ Oid tletype;
+ Oid atttype;
+
+ if (tle->resdom->resjunk)
+ continue;
+ tletype = exprType(tle->expr);
+ atttype = reln->rd_att->attrs[i]->atttypid;
+ if (!IsBinaryCompatible(tletype, atttype))
+ elog(ERROR, "function declared to return %s returns %s instead of %s at column %d",
+ format_type_be(rettype),
+ format_type_be(tletype),
+ format_type_be(atttype),
+ i + 1);
+ i++;
+ }
! /* this shouldn't happen, but let's just check... */
! if (i != relnatts)
! elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
! format_type_be(rettype), relnatts);
! heap_close(reln, AccessShareLock);
! return;
! }
! else if (fn_typtype == 'p' && rettype == RECORDOID)
! {
! /*
! * For RECORD return type, defer this check until we get the
! * first tuple.
! */
! return;
! }
! else
! elog(ERROR, "Unknown kind of return type specified for function");
}
***************
*** 540,545 ****
--- 560,566 ----
bool isnull;
Datum tmp;
char *prosrc;
+ char functyptype;
tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
if (!HeapTupleIsValid(tuple))
***************
*** 556,563 ****
prosrc = DatumGetCString(DirectFunctionCall1(textout, tmp));
querytree_list = pg_parse_and_rewrite(prosrc, proc->proargtypes, proc->pronargs);
! checkretval(proc->prorettype, querytree_list);
ReleaseSysCache(tuple);
PG_RETURN_BOOL(true);
--- 577,587 ----
prosrc = DatumGetCString(DirectFunctionCall1(textout, tmp));
+ /* check typtype to see if we have a predetermined return type */
+ functyptype = typeid_get_typtype(proc->prorettype);
+
querytree_list = pg_parse_and_rewrite(prosrc, proc->proargtypes, proc->pronargs);
! checkretval(proc->prorettype, functyptype, querytree_list);
ReleaseSysCache(tuple);
PG_RETURN_BOOL(true);
Index: src/backend/executor/functions.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/executor/functions.c,v
retrieving revision 1.52
diff -c -r1.52 functions.c
*** src/backend/executor/functions.c 20 Jun 2002 20:29:28 -0000 1.52
--- src/backend/executor/functions.c 27 Jul 2002 23:44:38 -0000
***************
*** 194,200 ****
* get the type length and by-value flag from the type tuple
*/
fcache->typlen = typeStruct->typlen;
! if (typeStruct->typrelid == InvalidOid)
{
/* The return type is not a relation, so just use byval */
fcache->typbyval = typeStruct->typbyval;
--- 194,201 ----
* get the type length and by-value flag from the type tuple
*/
fcache->typlen = typeStruct->typlen;
!
! if (typeStruct->typtype == 'b')
{
/* The return type is not a relation, so just use byval */
fcache->typbyval = typeStruct->typbyval;
Index: src/backend/executor/nodeFunctionscan.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/executor/nodeFunctionscan.c,v
retrieving revision 1.3
diff -c -r1.3 nodeFunctionscan.c
*** src/backend/executor/nodeFunctionscan.c 20 Jul 2002 05:16:58 -0000 1.3
--- src/backend/executor/nodeFunctionscan.c 29 Jul 2002 02:05:14 -0000
***************
*** 31,36 ****
--- 31,37 ----
#include "executor/nodeFunctionscan.h"
#include "parser/parsetree.h"
#include "parser/parse_expr.h"
+ #include "parser/parse_relation.h"
#include "parser/parse_type.h"
#include "storage/lmgr.h"
#include "tcop/pquery.h"
***************
*** 39,52 ****
#include "utils/tuplestore.h"
static TupleTableSlot *FunctionNext(FunctionScan *node);
! static TupleTableSlot *function_getonetuple(TupleTableSlot *slot,
! Node *expr,
! ExprContext *econtext,
! TupleDesc tupdesc,
! bool returnsTuple,
bool *isNull,
ExprDoneCond *isDone);
static FunctionMode get_functionmode(Node *expr);
/* ----------------------------------------------------------------
* Scan Support
--- 40,50 ----
#include "utils/tuplestore.h"
static TupleTableSlot *FunctionNext(FunctionScan *node);
! static TupleTableSlot *function_getonetuple(FunctionScanState *scanstate,
bool *isNull,
ExprDoneCond *isDone);
static FunctionMode get_functionmode(Node *expr);
+ static bool tupledesc_mismatch(TupleDesc tupdesc1, TupleDesc tupdesc2);
/* ----------------------------------------------------------------
* Scan Support
***************
*** 62,70 ****
FunctionNext(FunctionScan *node)
{
TupleTableSlot *slot;
- Node *expr;
- ExprContext *econtext;
- TupleDesc tupdesc;
EState *estate;
ScanDirection direction;
Tuplestorestate *tuplestorestate;
--- 60,65 ----
***************
*** 78,88 ****
scanstate = (FunctionScanState *) node->scan.scanstate;
estate = node->scan.plan.state;
direction = estate->es_direction;
- econtext = scanstate->csstate.cstate.cs_ExprContext;
tuplestorestate = scanstate->tuplestorestate;
- tupdesc = scanstate->tupdesc;
- expr = scanstate->funcexpr;
/*
* If first time through, read all tuples from function and pass them to
--- 73,80 ----
***************
*** 108,117 ****
isNull = false;
isDone = ExprSingleResult;
! slot = function_getonetuple(scanstate->csstate.css_ScanTupleSlot,
! expr, econtext, tupdesc,
! scanstate->returnsTuple,
! &isNull, &isDone);
if (TupIsNull(slot))
break;
--- 100,106 ----
isNull = false;
isDone = ExprSingleResult;
! slot = function_getonetuple(scanstate, &isNull, &isDone);
if (TupIsNull(slot))
break;
***************
*** 169,175 ****
RangeTblEntry *rte;
Oid funcrettype;
Oid funcrelid;
! TupleDesc tupdesc;
/*
* FunctionScan should not have any children.
--- 158,165 ----
RangeTblEntry *rte;
Oid funcrettype;
Oid funcrelid;
! char functyptype;
! TupleDesc tupdesc = NULL;
/*
* FunctionScan should not have any children.
***************
*** 209,233 ****
rte = rt_fetch(node->scan.scanrelid, estate->es_range_table);
Assert(rte->rtekind == RTE_FUNCTION);
funcrettype = exprType(rte->funcexpr);
! funcrelid = typeidTypeRelid(funcrettype);
/*
* Build a suitable tupledesc representing the output rows
*/
! if (OidIsValid(funcrelid))
{
! /*
! * Composite data type, i.e. a table's row type
! * Same as ordinary relation RTE
! */
! Relation rel;
! rel = relation_open(funcrelid, AccessShareLock);
! tupdesc = CreateTupleDescCopy(RelationGetDescr(rel));
! relation_close(rel, AccessShareLock);
! scanstate->returnsTuple = true;
}
! else
{
/*
* Must be a base data type, i.e. scalar
--- 199,234 ----
rte = rt_fetch(node->scan.scanrelid, estate->es_range_table);
Assert(rte->rtekind == RTE_FUNCTION);
funcrettype = exprType(rte->funcexpr);
!
! /*
! * Now determine if the function returns a simple or composite type,
! * and check/add column aliases.
! */
! functyptype = typeid_get_typtype(funcrettype);
/*
* Build a suitable tupledesc representing the output rows
*/
! if (functyptype == 'c')
{
! funcrelid = typeidTypeRelid(funcrettype);
! if (OidIsValid(funcrelid))
! {
! /*
! * Composite data type, i.e. a table's row type
! * Same as ordinary relation RTE
! */
! Relation rel;
! rel = relation_open(funcrelid, AccessShareLock);
! tupdesc = CreateTupleDescCopy(RelationGetDescr(rel));
! relation_close(rel, AccessShareLock);
! scanstate->returnsTuple = true;
! }
! else
! elog(ERROR, "Invalid return relation specified for function");
}
! else if (functyptype == 'b')
{
/*
* Must be a base data type, i.e. scalar
***************
*** 244,249 ****
--- 245,265 ----
false);
scanstate->returnsTuple = false;
}
+ else if (functyptype == 'p' && funcrettype == RECORDOID)
+ {
+ /*
+ * Must be a pseudo type, i.e. record
+ */
+ List *coldeflist = rte->coldeflist;
+
+ tupdesc = BuildDescForRelation(coldeflist);
+ scanstate->returnsTuple = true;
+ }
+ else
+ elog(ERROR, "Unknown kind of return type specified for function");
+
+ scanstate->fn_typeid = funcrettype;
+ scanstate->fn_typtype = functyptype;
scanstate->tupdesc = tupdesc;
ExecSetSlotDescriptor(scanstate->csstate.css_ScanTupleSlot,
tupdesc, false);
***************
*** 404,420 ****
* Run the underlying function to get the next tuple
*/
static TupleTableSlot *
! function_getonetuple(TupleTableSlot *slot,
! Node *expr,
! ExprContext *econtext,
! TupleDesc tupdesc,
! bool returnsTuple,
bool *isNull,
ExprDoneCond *isDone)
{
! HeapTuple tuple;
! Datum retDatum;
! char nullflag;
/*
* get the next Datum from the function
--- 420,439 ----
* Run the underlying function to get the next tuple
*/
static TupleTableSlot *
! function_getonetuple(FunctionScanState *scanstate,
bool *isNull,
ExprDoneCond *isDone)
{
! HeapTuple tuple;
! Datum retDatum;
! char nullflag;
! TupleDesc tupdesc = scanstate->tupdesc;
! bool returnsTuple = scanstate->returnsTuple;
! Node *expr = scanstate->funcexpr;
! Oid fn_typeid = scanstate->fn_typeid;
! char fn_typtype = scanstate->fn_typtype;
! ExprContext *econtext = scanstate->csstate.cstate.cs_ExprContext;
! TupleTableSlot *slot = scanstate->csstate.css_ScanTupleSlot;
/*
* get the next Datum from the function
***************
*** 435,440 ****
--- 454,469 ----
* function returns pointer to tts??
*/
slot = (TupleTableSlot *) retDatum;
+
+ /*
+ * if function return type was RECORD, we need to check to be
+ * sure the structure from the query matches the actual return
+ * structure
+ */
+ if (fn_typtype == 'p' && fn_typeid == RECORDOID)
+ if (tupledesc_mismatch(tupdesc, slot->ttc_tupleDescriptor))
+ elog(ERROR, "Query specified return tuple and actual"
+ " function return tuple do not match");
}
else
{
***************
*** 466,469 ****
--- 495,521 ----
* for the moment, hardwire this
*/
return PM_REPEATEDCALL;
+ }
+
+ static bool
+ tupledesc_mismatch(TupleDesc tupdesc1, TupleDesc tupdesc2)
+ {
+ int i;
+
+ if (tupdesc1->natts != tupdesc2->natts)
+ return true;
+
+ for (i = 0; i < tupdesc1->natts; i++)
+ {
+ Form_pg_attribute attr1 = tupdesc1->attrs[i];
+ Form_pg_attribute attr2 = tupdesc2->attrs[i];
+
+ /*
+ * We really only care about number of attributes and data type
+ */
+ if (attr1->atttypid != attr2->atttypid)
+ return true;
+ }
+
+ return false;
}
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.197
diff -c -r1.197 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 24 Jul 2002 19:11:10 -0000 1.197
--- src/backend/nodes/copyfuncs.c 27 Jul 2002 19:21:36 -0000
***************
*** 1482,1487 ****
--- 1482,1488 ----
newnode->relid = from->relid;
Node_Copy(from, newnode, subquery);
Node_Copy(from, newnode, funcexpr);
+ Node_Copy(from, newnode, coldeflist);
newnode->jointype = from->jointype;
Node_Copy(from, newnode, joinaliasvars);
Node_Copy(from, newnode, alias);
***************
*** 1707,1712 ****
--- 1708,1714 ----
Node_Copy(from, newnode, funccallnode);
Node_Copy(from, newnode, alias);
+ Node_Copy(from, newnode, coldeflist);
return newnode;
}
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.144
diff -c -r1.144 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 24 Jul 2002 19:11:10 -0000 1.144
--- src/backend/nodes/equalfuncs.c 27 Jul 2002 19:21:36 -0000
***************
*** 1579,1584 ****
--- 1579,1586 ----
return false;
if (!equal(a->alias, b->alias))
return false;
+ if (!equal(a->coldeflist, b->coldeflist))
+ return false;
return true;
}
***************
*** 1691,1696 ****
--- 1693,1700 ----
if (!equal(a->subquery, b->subquery))
return false;
if (!equal(a->funcexpr, b->funcexpr))
+ return false;
+ if (!equal(a->coldeflist, b->coldeflist))
return false;
if (a->jointype != b->jointype)
return false;
Index: src/backend/nodes/outfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/nodes/outfuncs.c,v
retrieving revision 1.165
diff -c -r1.165 outfuncs.c
*** src/backend/nodes/outfuncs.c 18 Jul 2002 17:14:19 -0000 1.165
--- src/backend/nodes/outfuncs.c 27 Jul 2002 19:21:36 -0000
***************
*** 1004,1009 ****
--- 1004,1011 ----
case RTE_FUNCTION:
appendStringInfo(str, ":funcexpr ");
_outNode(str, node->funcexpr);
+ appendStringInfo(str, ":coldeflist ");
+ _outNode(str, node->coldeflist);
break;
case RTE_JOIN:
appendStringInfo(str, ":jointype %d :joinaliasvars ",
Index: src/backend/nodes/readfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/nodes/readfuncs.c,v
retrieving revision 1.126
diff -c -r1.126 readfuncs.c
*** src/backend/nodes/readfuncs.c 18 Jul 2002 17:14:19 -0000 1.126
--- src/backend/nodes/readfuncs.c 27 Jul 2002 19:21:36 -0000
***************
*** 1545,1550 ****
--- 1545,1554 ----
case RTE_FUNCTION:
token = pg_strtok(&length); /* eat :funcexpr */
local_node->funcexpr = nodeRead(true); /* now read it */
+
+ token = pg_strtok(&length); /* eat :coldeflist */
+ local_node->coldeflist = nodeRead(true); /* now read it */
+
break;
case RTE_JOIN:
Index: src/backend/parser/gram.y
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.349
diff -c -r2.349 gram.y
*** src/backend/parser/gram.y 24 Jul 2002 19:11:10 -0000 2.349
--- src/backend/parser/gram.y 27 Jul 2002 19:21:36 -0000
***************
*** 218,224 ****
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit
%type <range> into_clause, OptTempTableName
--- 218,224 ----
target_list, update_target_list, insert_column_list,
insert_target_list, def_list, opt_indirection,
group_clause, TriggerFuncArgs, select_limit,
! opt_select_limit, tableFuncElementList
%type <range> into_clause, OptTempTableName
***************
*** 259,266 ****
%type <vsetstmt> set_rest
! %type <node> OptTableElement, ConstraintElem
! %type <node> columnDef
%type <defelt> def_elem
%type <node> def_arg, columnElem, where_clause, insert_column_item,
a_expr, b_expr, c_expr, r_expr, AexprConst,
--- 259,266 ----
%type <vsetstmt> set_rest
! %type <node> OptTableElement, ConstraintElem, tableFuncElement
! %type <node> columnDef, tableFuncColumnDef
%type <defelt> def_elem
%type <node> def_arg, columnElem, where_clause, insert_column_item,
a_expr, b_expr, c_expr, r_expr, AexprConst,
***************
*** 4373,4378 ****
--- 4373,4406 ----
{
RangeFunction *n = makeNode(RangeFunction);
n->funccallnode = $1;
+ n->coldeflist = NIL;
+ $$ = (Node *) n;
+ }
+ | func_table AS '(' tableFuncElementList ')'
+ {
+ RangeFunction *n = makeNode(RangeFunction);
+ n->funccallnode = $1;
+ n->coldeflist = $4;
+ $$ = (Node *) n;
+ }
+ | func_table AS ColId '(' tableFuncElementList ')'
+ {
+ RangeFunction *n = makeNode(RangeFunction);
+ Alias *a = makeNode(Alias);
+ n->funccallnode = $1;
+ a->aliasname = $3;
+ n->alias = a;
+ n->coldeflist = $5;
+ $$ = (Node *) n;
+ }
+ | func_table ColId '(' tableFuncElementList ')'
+ {
+ RangeFunction *n = makeNode(RangeFunction);
+ Alias *a = makeNode(Alias);
+ n->funccallnode = $1;
+ a->aliasname = $2;
+ n->alias = a;
+ n->coldeflist = $4;
$$ = (Node *) n;
}
| func_table alias_clause
***************
*** 4380,4385 ****
--- 4408,4414 ----
RangeFunction *n = makeNode(RangeFunction);
n->funccallnode = $1;
n->alias = $2;
+ n->coldeflist = NIL;
$$ = (Node *) n;
}
| select_with_parens
***************
*** 4620,4625 ****
--- 4649,4687 ----
| /*EMPTY*/ { $$ = NULL; }
;
+
+ tableFuncElementList:
+ tableFuncElementList ',' tableFuncElement
+ {
+ if ($3 != NULL)
+ $$ = lappend($1, $3);
+ else
+ $$ = $1;
+ }
+ | tableFuncElement
+ {
+ if ($1 != NULL)
+ $$ = makeList1($1);
+ else
+ $$ = NIL;
+ }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
+ tableFuncElement:
+ tableFuncColumnDef { $$ = $1; }
+ ;
+
+ tableFuncColumnDef: ColId Typename
+ {
+ ColumnDef *n = makeNode(ColumnDef);
+ n->colname = $1;
+ n->typename = $2;
+ n->constraints = NIL;
+
+ $$ = (Node *)n;
+ }
+ ;
/*****************************************************************************
*
Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/parser/parse_clause.c,v
retrieving revision 1.94
diff -c -r1.94 parse_clause.c
*** src/backend/parser/parse_clause.c 20 Jun 2002 20:29:32 -0000 1.94
--- src/backend/parser/parse_clause.c 27 Jul 2002 19:21:36 -0000
***************
*** 515,521 ****
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r->alias, true);
/*
* We create a RangeTblRef, but we do not add it to the joinlist or
--- 515,521 ----
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r, true);
/*
* We create a RangeTblRef, but we do not add it to the joinlist or
Index: src/backend/parser/parse_relation.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/parser/parse_relation.c,v
retrieving revision 1.70
diff -c -r1.70 parse_relation.c
*** src/backend/parser/parse_relation.c 20 Jun 2002 20:29:33 -0000 1.70
--- src/backend/parser/parse_relation.c 27 Jul 2002 20:00:42 -0000
***************
*** 681,692 ****
addRangeTableEntryForFunction(ParseState *pstate,
char *funcname,
Node *funcexpr,
! Alias *alias,
bool inFromCl)
{
RangeTblEntry *rte = makeNode(RangeTblEntry);
Oid funcrettype = exprType(funcexpr);
! Oid funcrelid;
Alias *eref;
int numaliases;
int varattno;
--- 681,694 ----
addRangeTableEntryForFunction(ParseState *pstate,
char *funcname,
Node *funcexpr,
! RangeFunction *rangefunc,
bool inFromCl)
{
RangeTblEntry *rte = makeNode(RangeTblEntry);
Oid funcrettype = exprType(funcexpr);
! char functyptype;
! Alias *alias = rangefunc->alias;
! List *coldeflist = rangefunc->coldeflist;
Alias *eref;
int numaliases;
int varattno;
***************
*** 695,700 ****
--- 697,703 ----
rte->relid = InvalidOid;
rte->subquery = NULL;
rte->funcexpr = funcexpr;
+ rte->coldeflist = coldeflist;
rte->alias = alias;
eref = alias ? (Alias *) copyObject(alias) : makeAlias(funcname, NIL);
***************
*** 706,752 ****
* Now determine if the function returns a simple or composite type,
* and check/add column aliases.
*/
! funcrelid = typeidTypeRelid(funcrettype);
! if (OidIsValid(funcrelid))
{
/*
! * Composite data type, i.e. a table's row type
! *
! * Get the rel's relcache entry. This access ensures that we have an
! * up-to-date relcache entry for the rel.
*/
! Relation rel;
! int maxattrs;
! rel = heap_open(funcrelid, AccessShareLock);
! /*
! * Since the rel is open anyway, let's check that the number of column
! * aliases is reasonable.
! */
! maxattrs = RelationGetNumberOfAttributes(rel);
! if (maxattrs < numaliases)
! elog(ERROR, "Table \"%s\" has %d columns available but %d columns specified",
! RelationGetRelationName(rel), maxattrs, numaliases);
! /* fill in alias columns using actual column names */
! for (varattno = numaliases; varattno < maxattrs; varattno++)
! {
! char *attrname;
! attrname = pstrdup(NameStr(rel->rd_att->attrs[varattno]->attname));
! eref->colnames = lappend(eref->colnames, makeString(attrname));
}
!
! /*
! * Drop the rel refcount, but keep the access lock till end of
! * transaction so that the table can't be deleted or have its schema
! * modified underneath us.
! */
! heap_close(rel, NoLock);
}
! else
{
/*
* Must be a base data type, i.e. scalar.
--- 709,764 ----
* Now determine if the function returns a simple or composite type,
* and check/add column aliases.
*/
! functyptype = typeid_get_typtype(funcrettype);
! if (functyptype == 'c')
{
/*
! * Named composite data type, i.e. a table's row type
*/
! Oid funcrelid = typeidTypeRelid(funcrettype);
! if (OidIsValid(funcrelid))
! {
! /*
! * Get the rel's relcache entry. This access ensures that we have an
! * up-to-date relcache entry for the rel.
! */
! Relation rel;
! int maxattrs;
!
! rel = heap_open(funcrelid, AccessShareLock);
!
! /*
! * Since the rel is open anyway, let's check that the number of column
! * aliases is reasonable.
! */
! maxattrs = RelationGetNumberOfAttributes(rel);
! if (maxattrs < numaliases)
! elog(ERROR, "Table \"%s\" has %d columns available but %d columns specified",
! RelationGetRelationName(rel), maxattrs, numaliases);
! /* fill in alias columns using actual column names */
! for (varattno = numaliases; varattno < maxattrs; varattno++)
! {
! char *attrname;
! attrname = pstrdup(NameStr(rel->rd_att->attrs[varattno]->attname));
! eref->colnames = lappend(eref->colnames, makeString(attrname));
! }
! /*
! * Drop the rel refcount, but keep the access lock till end of
! * transaction so that the table can't be deleted or have its schema
! * modified underneath us.
! */
! heap_close(rel, NoLock);
}
! else
! elog(ERROR, "Invalid return relation specified for function %s",
! funcname);
}
! else if (functyptype == 'b')
{
/*
* Must be a base data type, i.e. scalar.
***************
*** 758,763 ****
--- 770,791 ----
if (numaliases == 0)
eref->colnames = makeList1(makeString(funcname));
}
+ else if (functyptype == 'p' && funcrettype == RECORDOID)
+ {
+ List *col;
+
+ foreach(col, coldeflist)
+ {
+ char *attrname;
+ ColumnDef *n = lfirst(col);
+
+ attrname = pstrdup(n->colname);
+ eref->colnames = lappend(eref->colnames, makeString(attrname));
+ }
+ }
+ else
+ elog(ERROR, "Unknown kind of return type specified for function %s",
+ funcname);
/*----------
* Flags:
***************
*** 1030,1082 ****
case RTE_FUNCTION:
{
/* Function RTE */
! Oid funcrettype = exprType(rte->funcexpr);
! Oid funcrelid = typeidTypeRelid(funcrettype);
!
! if (OidIsValid(funcrelid))
{
! /*
! * Composite data type, i.e. a table's row type
! * Same as ordinary relation RTE
! */
! Relation rel;
! int maxattrs;
! int numaliases;
!
! rel = heap_open(funcrelid, AccessShareLock);
! maxattrs = RelationGetNumberOfAttributes(rel);
! numaliases = length(rte->eref->colnames);
!
! for (varattno = 0; varattno < maxattrs; varattno++)
{
- Form_pg_attribute attr = rel->rd_att->attrs[varattno];
! if (colnames)
! {
! char *label;
!
! if (varattno < numaliases)
! label = strVal(nth(varattno, rte->eref->colnames));
! else
! label = NameStr(attr->attname);
! *colnames = lappend(*colnames, makeString(pstrdup(label)));
! }
! if (colvars)
{
! Var *varnode;
! varnode = makeVar(rtindex, attr->attnum,
! attr->atttypid, attr->atttypmod,
! sublevels_up);
! *colvars = lappend(*colvars, varnode);
}
- }
! heap_close(rel, AccessShareLock);
}
! else
{
/*
* Must be a base data type, i.e. scalar
--- 1058,1124 ----
case RTE_FUNCTION:
{
/* Function RTE */
! Oid funcrettype = exprType(rte->funcexpr);
! char functyptype = typeid_get_typtype(funcrettype);
! List *coldeflist = rte->coldeflist;
!
! /*
! * Build a suitable tupledesc representing the output rows
! */
! if (functyptype == 'c')
{
! Oid funcrelid = typeidTypeRelid(funcrettype);
! if (OidIsValid(funcrelid))
{
! /*
! * Composite data type, i.e. a table's row type
! * Same as ordinary relation RTE
! */
! Relation rel;
! int maxattrs;
! int numaliases;
!
! rel = heap_open(funcrelid, AccessShareLock);
! maxattrs = RelationGetNumberOfAttributes(rel);
! numaliases = length(rte->eref->colnames);
! for (varattno = 0; varattno < maxattrs; varattno++)
{
! Form_pg_attribute attr = rel->rd_att->attrs[varattno];
! if (colnames)
! {
! char *label;
!
! if (varattno < numaliases)
! label = strVal(nth(varattno, rte->eref->colnames));
! else
! label = NameStr(attr->attname);
! *colnames = lappend(*colnames, makeString(pstrdup(label)));
! }
!
! if (colvars)
! {
! Var *varnode;
!
! varnode = makeVar(rtindex,
! attr->attnum,
! attr->atttypid,
! attr->atttypmod,
! sublevels_up);
! *colvars = lappend(*colvars, varnode);
! }
}
! heap_close(rel, AccessShareLock);
! }
! else
! elog(ERROR, "Invalid return relation specified"
! " for function");
}
! else if (functyptype == 'b')
{
/*
* Must be a base data type, i.e. scalar
***************
*** 1096,1101 ****
--- 1138,1184 ----
*colvars = lappend(*colvars, varnode);
}
}
+ else if (functyptype == 'p' && funcrettype == RECORDOID)
+ {
+ List *col;
+ int attnum = 0;
+
+ foreach(col, coldeflist)
+ {
+ ColumnDef *colDef = lfirst(col);
+
+ attnum++;
+ if (colnames)
+ {
+ char *attrname;
+
+ attrname = pstrdup(colDef->colname);
+ *colnames = lappend(*colnames, makeString(attrname));
+ }
+
+ if (colvars)
+ {
+ Var *varnode;
+ HeapTuple typeTuple;
+ Oid atttypid;
+
+ typeTuple = typenameType(colDef->typename);
+ atttypid = HeapTupleGetOid(typeTuple);
+ ReleaseSysCache(typeTuple);
+
+ varnode = makeVar(rtindex,
+ attnum,
+ atttypid,
+ -1,
+ sublevels_up);
+
+ *colvars = lappend(*colvars, varnode);
+ }
+ }
+ }
+ else
+ elog(ERROR, "Unknown kind of return type specified"
+ " for function");
}
break;
case RTE_JOIN:
***************
*** 1277,1308 ****
case RTE_FUNCTION:
{
/* Function RTE */
! Oid funcrettype = exprType(rte->funcexpr);
! Oid funcrelid = typeidTypeRelid(funcrettype);
!
! if (OidIsValid(funcrelid))
{
/*
* Composite data type, i.e. a table's row type
* Same as ordinary relation RTE
*/
! HeapTuple tp;
! Form_pg_attribute att_tup;
! tp = SearchSysCache(ATTNUM,
! ObjectIdGetDatum(funcrelid),
! Int16GetDatum(attnum),
! 0, 0);
! /* this shouldn't happen... */
! if (!HeapTupleIsValid(tp))
! elog(ERROR, "Relation %s does not have attribute %d",
! get_rel_name(funcrelid), attnum);
! att_tup = (Form_pg_attribute) GETSTRUCT(tp);
! *vartype = att_tup->atttypid;
! *vartypmod = att_tup->atttypmod;
! ReleaseSysCache(tp);
}
! else
{
/*
* Must be a base data type, i.e. scalar
--- 1360,1403 ----
case RTE_FUNCTION:
{
/* Function RTE */
! Oid funcrettype = exprType(rte->funcexpr);
! char functyptype = typeid_get_typtype(funcrettype);
! List *coldeflist = rte->coldeflist;
!
! /*
! * Build a suitable tupledesc representing the output rows
! */
! if (functyptype == 'c')
{
/*
* Composite data type, i.e. a table's row type
* Same as ordinary relation RTE
*/
! Oid funcrelid = typeidTypeRelid(funcrettype);
!
! if (OidIsValid(funcrelid))
! {
! HeapTuple tp;
! Form_pg_attribute att_tup;
! tp = SearchSysCache(ATTNUM,
! ObjectIdGetDatum(funcrelid),
! Int16GetDatum(attnum),
! 0, 0);
! /* this shouldn't happen... */
! if (!HeapTupleIsValid(tp))
! elog(ERROR, "Relation %s does not have attribute %d",
! get_rel_name(funcrelid), attnum);
! att_tup = (Form_pg_attribute) GETSTRUCT(tp);
! *vartype = att_tup->atttypid;
! *vartypmod = att_tup->atttypmod;
! ReleaseSysCache(tp);
! }
! else
! elog(ERROR, "Invalid return relation specified"
! " for function");
}
! else if (functyptype == 'b')
{
/*
* Must be a base data type, i.e. scalar
***************
*** 1310,1315 ****
--- 1405,1426 ----
*vartype = funcrettype;
*vartypmod = -1;
}
+ else if (functyptype == 'p' && funcrettype == RECORDOID)
+ {
+ ColumnDef *colDef = nth(attnum - 1, coldeflist);
+ HeapTuple typeTuple;
+ Oid atttypid;
+
+ typeTuple = typenameType(colDef->typename);
+ atttypid = HeapTupleGetOid(typeTuple);
+ ReleaseSysCache(typeTuple);
+
+ *vartype = atttypid;
+ *vartypmod = -1;
+ }
+ else
+ elog(ERROR, "Unknown kind of return type specified"
+ " for function");
}
break;
case RTE_JOIN:
***************
*** 1448,1451 ****
--- 1559,1587 ----
elog(NOTICE, "Adding missing FROM-clause entry%s for table \"%s\"",
pstate->parentParseState != NULL ? " in subquery" : "",
relation->relname);
+ }
+
+ char
+ typeid_get_typtype(Oid typeid)
+ {
+ HeapTuple typeTuple;
+ Form_pg_type typeStruct;
+ char result;
+
+ /*
+ * determine if the function returns a simple, named composite,
+ * or anonymous composite type
+ */
+ typeTuple = SearchSysCache(TYPEOID,
+ ObjectIdGetDatum(typeid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(typeTuple))
+ elog(ERROR, "cache lookup for type %u failed", typeid);
+ typeStruct = (Form_pg_type) GETSTRUCT(typeTuple);
+
+ result = typeStruct->typtype;
+
+ ReleaseSysCache(typeTuple);
+
+ return result;
}
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.125
diff -c -r1.125 pg_type.h
*** src/include/catalog/pg_type.h 24 Jul 2002 19:11:13 -0000 1.125
--- src/include/catalog/pg_type.h 27 Jul 2002 19:58:03 -0000
***************
*** 60,69 ****
bool typbyval;
/*
! * typtype is 'b' for a basic type and 'c' for a catalog type (ie a
! * class). If typtype is 'c', typrelid is the OID of the class' entry
! * in pg_class. (Why do we need an entry in pg_type for classes,
! * anyway?)
*/
char typtype;
--- 60,69 ----
bool typbyval;
/*
! * typtype is 'b' for a basic type, 'c' for a catalog type (ie a
! * class), or 'p' for a pseudo type. If typtype is 'c', typrelid is the
! * OID of the class' entry in pg_class. (Why do we need an entry in
! * pg_type for classes, anyway?)
*/
char typtype;
***************
*** 501,506 ****
--- 501,516 ----
DATA(insert OID = 2210 ( _regclass PGNSP PGUID -1 f b t \054 0 2205 array_in array_out i x f 0 -1 0 _null_ _null_
));
DATA(insert OID = 2211 ( _regtype PGNSP PGUID -1 f b t \054 0 2206 array_in array_out i x f 0 -1 0 _null_ _null_
));
+ /*
+ * pseudo-types
+ *
+ * types with typtype='p' are special types that represent classes of types
+ * that are not easily defined in advance. Currently there is only one pseudo
+ * type -- record. The record type is used to specify that the value is a
+ * tuple, but of unknown structure until runtime.
+ */
+ DATA(insert OID = 2249 ( record PGNSP PGUID 4 t p t \054 0 0 oidin oidout i p f 0 -1 0 _null_ _null_
));
+ #define RECORDOID 2249
/*
* prototypes for functions in pg_type.c
Index: src/include/nodes/execnodes.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/nodes/execnodes.h,v
retrieving revision 1.70
diff -c -r1.70 execnodes.h
*** src/include/nodes/execnodes.h 20 Jun 2002 20:29:49 -0000 1.70
--- src/include/nodes/execnodes.h 28 Jul 2002 22:09:25 -0000
***************
*** 509,519 ****
* Function nodes are used to scan the results of a
* function appearing in FROM (typically a function returning set).
*
! * functionmode function operating mode:
* - repeated call
* - materialize
* - return query
* tuplestorestate private state of tuplestore.c
* ----------------
*/
typedef enum FunctionMode
--- 509,525 ----
* Function nodes are used to scan the results of a
* function appearing in FROM (typically a function returning set).
*
! * functionmode function operating mode:
* - repeated call
* - materialize
* - return query
+ * tupdesc function's return tuple description
* tuplestorestate private state of tuplestore.c
+ * funcexpr function expression being evaluated
+ * returnsTuple does function return tuples?
+ * fn_typeid OID of function return type
+ * fn_typtype return Datum type, i.e. 'b'ase,
+ * 'c'atalog, or 'p'seudo
* ----------------
*/
typedef enum FunctionMode
***************
*** 525,536 ****
typedef struct FunctionScanState
{
! CommonScanState csstate; /* its first field is NodeTag */
FunctionMode functionmode;
TupleDesc tupdesc;
void *tuplestorestate;
! Node *funcexpr; /* function expression being evaluated */
! bool returnsTuple; /* does function return tuples? */
} FunctionScanState;
/* ----------------------------------------------------------------
--- 531,544 ----
typedef struct FunctionScanState
{
! CommonScanState csstate; /* its first field is NodeTag */
FunctionMode functionmode;
TupleDesc tupdesc;
void *tuplestorestate;
! Node *funcexpr;
! bool returnsTuple;
! Oid fn_typeid;
! char fn_typtype;
} FunctionScanState;
/* ----------------------------------------------------------------
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.194
diff -c -r1.194 parsenodes.h
*** src/include/nodes/parsenodes.h 24 Jul 2002 19:11:14 -0000 1.194
--- src/include/nodes/parsenodes.h 27 Jul 2002 19:21:36 -0000
***************
*** 400,405 ****
--- 400,407 ----
NodeTag type;
Node *funccallnode; /* untransformed function call tree */
Alias *alias; /* table alias & optional column aliases */
+ List *coldeflist; /* list of ColumnDef nodes for runtime
+ * assignment of RECORD TupleDesc */
} RangeFunction;
/*
***************
*** 527,532 ****
--- 529,536 ----
* Fields valid for a function RTE (else NULL):
*/
Node *funcexpr; /* expression tree for func call */
+ List *coldeflist; /* list of ColumnDef nodes for runtime
+ * assignment of RECORD TupleDesc */
/*
* Fields valid for a join RTE (else NULL/zero):
Index: src/include/parser/parse_relation.h
===================================================================
RCS file: /opt/src/cvs/pgsql/src/include/parser/parse_relation.h,v
retrieving revision 1.34
diff -c -r1.34 parse_relation.h
*** src/include/parser/parse_relation.h 20 Jun 2002 20:29:51 -0000 1.34
--- src/include/parser/parse_relation.h 27 Jul 2002 19:21:36 -0000
***************
*** 44,50 ****
extern RangeTblEntry *addRangeTableEntryForFunction(ParseState *pstate,
char *funcname,
Node *funcexpr,
! Alias *alias,
bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
List *colnames,
--- 44,50 ----
extern RangeTblEntry *addRangeTableEntryForFunction(ParseState *pstate,
char *funcname,
Node *funcexpr,
! RangeFunction *rangefunc,
bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate,
List *colnames,
***************
*** 61,65 ****
--- 61,66 ----
extern int attnameAttNum(Relation rd, char *a);
extern Name attnumAttName(Relation rd, int attid);
extern Oid attnumTypeId(Relation rd, int attid);
+ extern char typeid_get_typtype(Oid typeid);
#endif /* PARSE_RELATION_H */
Index: src/test/regress/expected/type_sanity.out
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/expected/type_sanity.out,v
retrieving revision 1.9
diff -c -r1.9 type_sanity.out
*** src/test/regress/expected/type_sanity.out 24 Jul 2002 19:11:14 -0000 1.9
--- src/test/regress/expected/type_sanity.out 29 Jul 2002 00:56:57 -0000
***************
*** 16,22 ****
SELECT p1.oid, p1.typname
FROM pg_type as p1
WHERE (p1.typlen <= 0 AND p1.typlen != -1) OR
! (p1.typtype != 'b' AND p1.typtype != 'c') OR
NOT p1.typisdefined OR
(p1.typalign != 'c' AND p1.typalign != 's' AND
p1.typalign != 'i' AND p1.typalign != 'd') OR
--- 16,22 ----
SELECT p1.oid, p1.typname
FROM pg_type as p1
WHERE (p1.typlen <= 0 AND p1.typlen != -1) OR
! (p1.typtype != 'b' AND p1.typtype != 'c' AND p1.typtype != 'p') OR
NOT p1.typisdefined OR
(p1.typalign != 'c' AND p1.typalign != 's' AND
p1.typalign != 'i' AND p1.typalign != 'd') OR
***************
*** 60,66 ****
-- NOTE: as of 7.3, this check finds SET, smgr, and unknown.
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype != 'c' AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
--- 60,66 ----
-- NOTE: as of 7.3, this check finds SET, smgr, and unknown.
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype = 'b' AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
Index: src/test/regress/sql/type_sanity.sql
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/sql/type_sanity.sql,v
retrieving revision 1.9
diff -c -r1.9 type_sanity.sql
*** src/test/regress/sql/type_sanity.sql 24 Jul 2002 19:11:14 -0000 1.9
--- src/test/regress/sql/type_sanity.sql 29 Jul 2002 00:52:41 -0000
***************
*** 19,25 ****
SELECT p1.oid, p1.typname
FROM pg_type as p1
WHERE (p1.typlen <= 0 AND p1.typlen != -1) OR
! (p1.typtype != 'b' AND p1.typtype != 'c') OR
NOT p1.typisdefined OR
(p1.typalign != 'c' AND p1.typalign != 's' AND
p1.typalign != 'i' AND p1.typalign != 'd') OR
--- 19,25 ----
SELECT p1.oid, p1.typname
FROM pg_type as p1
WHERE (p1.typlen <= 0 AND p1.typlen != -1) OR
! (p1.typtype != 'b' AND p1.typtype != 'c' AND p1.typtype != 'p') OR
NOT p1.typisdefined OR
(p1.typalign != 'c' AND p1.typalign != 's' AND
p1.typalign != 'i' AND p1.typalign != 'd') OR
***************
*** 55,61 ****
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype != 'c' AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
--- 55,61 ----
SELECT p1.oid, p1.typname
FROM pg_type as p1
! WHERE p1.typtype = 'b' AND p1.typname NOT LIKE '\\_%' AND NOT EXISTS
(SELECT 1 FROM pg_type as p2
WHERE p2.typname = ('_' || p1.typname)::name AND
p2.typelem = p1.oid);
Index: doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.54
diff -c -r1.54 select.sgml
*** doc/src/sgml/ref/select.sgml 23 Apr 2002 02:07:16 -0000 1.54
--- doc/src/sgml/ref/select.sgml 29 Jul 2002 04:16:51 -0000
***************
*** 40,45 ****
--- 40,51 ----
( <replaceable class="PARAMETER">select</replaceable> )
[ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable
class="PARAMETER">column_alias_list</replaceable>) ]
|
+ <replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable
class="parameter">argtype</replaceable>[, ...] ] )
+ [ AS ] <replaceable class="PARAMETER">alias</replaceable> [ ( <replaceable
class="PARAMETER">column_alias_list</replaceable>| <replaceable class="PARAMETER">column_definition_list</replaceable>
)]
+ |
+ <replaceable class="PARAMETER">table_function_name</replaceable> ( [ <replaceable
class="parameter">argtype</replaceable>[, ...] ] )
+ AS ( <replaceable class="PARAMETER">column_definition_list</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_list</replaceable>) ]
</synopsis>
***************
*** 82,88 ****
<term><replaceable class="PARAMETER">from_item</replaceable></term>
<listitem>
<para>
! A table reference, sub-SELECT, or JOIN clause. See below for details.
</para>
</listitem>
</varlistentry>
--- 88,94 ----
<term><replaceable class="PARAMETER">from_item</replaceable></term>
<listitem>
<para>
! A table reference, sub-SELECT, table function, or JOIN clause. See below for details.
</para>
</listitem>
</varlistentry>
***************
*** 156,161 ****
--- 162,184 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">table function</replaceable></term>
+ <listitem>
+ <para>
+ A table function can appear in the FROM clause. This acts as though
+ its output were created as a temporary table for the duration of
+ this single SELECT command. An alias may also be used. If an alias is
+ written, a column alias list can also be written to provide substitute names
+ for one or more columns of the table function. If the table function has been
+ defined as returning the RECORD data type, an alias, or the keyword AS, must
+ also be present, followed by a column definition list in the form
+ ( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable>
[,... ] ).
+ The column definition list must match the actual number and types returned by the function.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">join_type</replaceable></term>
***************
*** 381,386 ****
--- 404,422 ----
</para>
<para>
+ A FROM item can be a table function (i.e. a function that returns
+ multiple rows and columns). When a table function is created, it may
+ be defined to return a named scalar or composite data type (an existing
+ scalar data type, or a table or view name), or it may be defined to return
+ a RECORD data type. When a table function is defined to return RECORD, it
+ must be followed in the FROM clause by an alias, or the keyword AS alone,
+ and then by a parenthesized list of column names and types. This provides
+ a query-time composite type definition. The FROM clause composite type
+ must match the actual composite type returned from the function or an
+ ERROR will be generated.
+ </para>
+
+ <para>
Finally, a FROM item can be a JOIN clause, which combines two simpler
FROM items. (Use parentheses if necessary to determine the order
of nesting.)
***************
*** 925,930 ****
--- 961,1003 ----
Warren Beatty
Westward
Woody Allen
+ </programlisting>
+ </para>
+
+ <para>
+ This example shows how to use a table function, both with and without
+ a column definition list.
+
+ <programlisting>
+ distributors:
+ did | name
+ -----+--------------
+ 108 | Westward
+ 111 | Walt Disney
+ 112 | Warner Bros.
+ ...
+
+ CREATE FUNCTION distributors(int)
+ RETURNS SETOF distributors AS '
+ SELECT * FROM distributors WHERE did = $1;
+ ' LANGUAGE SQL;
+
+ SELECT * FROM distributors(111);
+ did | name
+ -----+-------------
+ 111 | Walt Disney
+ (1 row)
+
+ CREATE FUNCTION distributors_2(int)
+ RETURNS SETOF RECORD AS '
+ SELECT * FROM distributors WHERE did = $1;
+ ' LANGUAGE SQL;
+
+ SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
+ f1 | f2
+ -----+-------------
+ 111 | Walt Disney
+ (1 row)
</programlisting>
</para>
</refsect1>
pgsql-patches by date: