Re: anonymous composite types for Table Functions (aka SRFs) - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: anonymous composite types for Table Functions (aka SRFs)
Date
Msg-id 200208012330.g71NUaj19391@candle.pha.pa.us
Whole thread Raw
In response to anonymous composite types for Table Functions (aka SRFs)  (Joe Conway <mail@joeconway.com>)
List pgsql-patches
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Joe Conway wrote:
> 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>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: small psql patch - show Schema name for \dt \dv \dS
Next
From: "Christopher Kings-Lynne"
Date:
Subject: Re: New contrib: pg_reset_stats()