Thread: anonymous composite types for Table Functions (aka SRFs)

anonymous composite types for Table Functions (aka SRFs)

From

Joe Conway

Date:

29 July 2002, 04:25:29

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>

Re: anonymous composite types for Table Functions (aka SRFs)

From

nconway@klamath.dyndns.org (Neil Conway)

Date:

29 July 2002, 13:57:36

On Sun, Jul 28, 2002 at 10:24:34PM -0700, Joe Conway wrote:
> Attached are two patches to implement and document anonymous composite
> types for Table Functions, as previously proposed on HACKERS.

Nice work!

> 1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
>     'b' for base or 'c' for catalog, i.e. a class).

I think you mentioned that typtype could be renamed to typkind -- that
sounds good to me...

> 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)

Is there a reason why you can't specify the return type in the function
declaration? ISTM that for most functions, the 'AS' clause will be the
same for every usage of the function.

On a related note, it is possible for the table function to examine the
attributes it has been asked to return, right? Since the current patch
requires that every call specify the return type, it might be possible
to take advantage of that to provide semi-"polymorphic" behavior
(i.e. the function behaves differently depending on the type of data
the user asked for)

>     SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)

What does the 'f' indicate?

>     SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)

This form of the syntax seems a bit unclear, IMHO. It seems a bit
like two function calls. Can the 'AS' be made mandatory?

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

29 July 2002, 14:03:42

nconway@klamath.dyndns.org (Neil Conway) writes:
>> 1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
>> 'b' for base or 'c' for catalog, i.e. a class).

> I think you mentioned that typtype could be renamed to typkind -- that
> sounds good to me...

It sounds like a way to break client-side code for little gain to me...

> Is there a reason why you can't specify the return type in the function
> declaration? ISTM that for most functions, the 'AS' clause will be the
> same for every usage of the function.

The particular functions Joe is worried about (dblink and such) do not
have a fixed return type.  In any case that would be a separate
mechanism with its own issues, because we'd have to store the anonymous
type in the system catalogs.

>> SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)

> What does the 'f' indicate?

It's required by the SQL alias syntax.

>> SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)

> This form of the syntax seems a bit unclear, IMHO. It seems a bit
> like two function calls. Can the 'AS' be made mandatory?

Why?  That just deviates even further from the spec syntax.

            regards, tom lane

Re: anonymous composite types for Table Functions (aka SRFs)

From

nconway@klamath.dyndns.org (Neil Conway)

Date:

29 July 2002, 14:24:03

On Mon, Jul 29, 2002 at 11:03:40AM -0400, Tom Lane wrote:
> nconway@klamath.dyndns.org (Neil Conway) writes:
> > Is there a reason why you can't specify the return type in the function
> > declaration? ISTM that for most functions, the 'AS' clause will be the
> > same for every usage of the function.
>
> The particular functions Joe is worried about (dblink and such) do not
> have a fixed return type.

Right -- so when you declare the SRF, you could be allowed to define
a composite type that will be used if the caller doesn't specify one
(i.e. the default return type). This wouldn't get us a whole lot over
the existing 'CREATE VIEW' hack, except it would be cleaner.

> In any case that would be a separate
> mechanism with its own issues, because we'd have to store the anonymous
> type in the system catalogs.

Ok -- it still seems worthwhile to me.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

29 July 2002, 14:31:38

Neil Conway wrote:
> On Sun, Jul 28, 2002 at 10:24:34PM -0700, Joe Conway wrote:
>>Attached are two patches to implement and document anonymous composite
>>types for Table Functions, as previously proposed on HACKERS.
>
> Nice work!

Thanks!

>>1. Creates a new pg_type typtype: 'p' for pseudo type (currently either
>>    'b' for base or 'c' for catalog, i.e. a class).
> I think you mentioned that typtype could be renamed to typkind -- that
> sounds good to me...

I didn't get any feedback on that idea, so I decided to leave it alone
for now.

>>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)
>
> Is there a reason why you can't specify the return type in the function
> declaration? ISTM that for most functions, the 'AS' clause will be the
> same for every usage of the function.

Ahh, that's the next proposal ;-)

For functions such as dblink and crosstab, mentioned in the original
post, the 'AS' clause needed may be *different* every time the function
is called.

But specifying the composite type on function declaration would also be
a good thing. In order to do that we first need to be able to create
named, but stand-alone, composite types. Once we can do that, then
creating an implicit named composite type (with a system generated name)
on function creation should be easy. I plan to follow up with a specific
proposal this week.

> On a related note, it is possible for the table function to examine the
> attributes it has been asked to return, right? Since the current patch
> requires that every call specify the return type, it might be possible
> to take advantage of that to provide semi-"polymorphic" behavior
> (i.e. the function behaves differently depending on the type of data
> the user asked for)

Hmmm, I'll have to think about that. I don't think the current
implementation has a way to pass that information to the function. We
would need to modify fcinfo to carry along the run-time tupdesc if we
wanted to do this. Not sure how hard it would be to do -- I'll have to look.

Note that this syntax is only required when the function has been
declared as returning RECORD -- if the function is declared to return
some named composite type, it doesn't need or use the runtime specified
structure. In that case the function *can* derive it's own tupdesc using
the function return type to get the return type relid. That is how the
submitted crosstab() function works.

>>    SELECT * from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)
>
>
> What does the 'f' indicate?

'f' here is the table alias. Probably would have been more clear like this:

    SELECT f.* from foo(sqlstmt) AS f(f1 int, f2 text, f3 timestamp)

>>    SELECT * from foo(sqlstmt) f(f1 int, f2 text, f3 timestamp)
> This form of the syntax seems a bit unclear, IMHO. It seems a bit
> like two function calls. Can the 'AS' be made mandatory?

Well this is currently one acceptable syntax for an alias clause, e.g.
    SELECT * from foo f(f1, f2, f3);

is allowed. I was trying to be consistent. Anyone else have thoughts on
this?

Thank you for your review and comments!

Joe

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

01 August 2002, 22:30:52

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

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

04 August 2002, 03:58:53

I am sorry but I am unable to apply this patch because of the DROP
COLUMN patch that was applied since you submitted this.

It had rejections in gram.y and parse_relation.c, but those were easy to
fix.  The big problem is pg_proc.c, where the code changes can not be
merged.

I am attaching the rejected part of the patch.  If you can send me a
fixed version of just that change, I can commit the rest.

Thanks.


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

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.
>

--
  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
***************
*** 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");
  }

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

04 August 2002, 05:54:32

Bruce Momjian wrote:
> I am sorry but I am unable to apply this patch because of the DROP
> COLUMN patch that was applied since you submitted this.
>
> It had rejections in gram.y and parse_relation.c, but those were easy to
> fix.  The big problem is pg_proc.c, where the code changes can not be
> merged.
>
> I am attaching the rejected part of the patch.  If you can send me a
> fixed version of just that change, I can commit the rest.
>

OK. Here is a patch against current cvs for just pg_proc.c. This
includes all the changes for that file (i.e. not just the one rejected
hunk).

Thanks,

Joe


Index: src/backend/catalog/pg_proc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/backend/catalog/pg_proc.c,v
retrieving revision 1.82
diff -c -r1.82 pg_proc.c
*** src/backend/catalog/pg_proc.c    2 Aug 2002 18:15:05 -0000    1.82
--- src/backend/catalog/pg_proc.c    4 Aug 2002 06:21:51 -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);
***************
*** 367,460 ****
       */
      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 scan the non-deleted attributes to ensure
!      * that they match the datatypes of the non-resjunk columns.
!      */
!     reln = heap_open(typerelid, AccessShareLock);
!     relnatts = reln->rd_rel->relnatts;
!     rellogcols = 0;                /* we'll count nondeleted cols as we go */
!     colindex = 0;
!
!     foreach(tlistitem, tlist)
!     {
!         TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
!         Form_pg_attribute attr;
!         Oid            tletype;
!         Oid            atttype;

!         if (tle->resdom->resjunk)
!             continue;

!         do {
              colindex++;
              if (colindex > relnatts)
!                 elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
!                      format_type_be(rettype), rellogcols);
!             attr = reln->rd_att->attrs[colindex - 1];
!         } while (attr->attisdropped);
!         rellogcols++;
!
!         tletype = exprType(tle->expr);
!         atttype = attr->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),
!                  rellogcols);
!     }
!
!     for (;;)
!     {
!         colindex++;
!         if (colindex > relnatts)
!             break;
!         if (!reln->rd_att->attrs[colindex - 1]->attisdropped)
!             rellogcols++;
!     }

!     if (tlistlen != rellogcols)
!         elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
!              format_type_be(rettype), rellogcols);

!     heap_close(reln, AccessShareLock);
  }


--- 368,480 ----
       */
      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 scan the non-deleted attributes to ensure
+          * that they match the datatypes of the non-resjunk columns.
+          */
+         reln = heap_open(typerelid, AccessShareLock);
+         relnatts = reln->rd_rel->relnatts;
+         rellogcols = 0;                /* we'll count nondeleted cols as we go */
+         colindex = 0;

!         foreach(tlistitem, tlist)
!         {
!             TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
!             Form_pg_attribute attr;
!             Oid            tletype;
!             Oid            atttype;
!
!             if (tle->resdom->resjunk)
!                 continue;
!
!             do {
!                 colindex++;
!                 if (colindex > relnatts)
!                     elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
!                          format_type_be(rettype), rellogcols);
!                 attr = reln->rd_att->attrs[colindex - 1];
!             } while (attr->attisdropped);
!             rellogcols++;

!             tletype = exprType(tle->expr);
!             atttype = attr->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),
!                      rellogcols);
!         }

!         for (;;)
!         {
              colindex++;
              if (colindex > relnatts)
!                 break;
!             if (!reln->rd_att->attrs[colindex - 1]->attisdropped)
!                 rellogcols++;
!         }

!         if (tlistlen != rellogcols)
!             elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
!                  format_type_be(rettype), rellogcols);

!         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");
  }


***************
*** 553,558 ****
--- 573,579 ----
      bool        isnull;
      Datum        tmp;
      char       *prosrc;
+     char        functyptype;

      tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
      if (!HeapTupleIsValid(tuple))
***************
*** 569,576 ****

      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);
--- 590,600 ----

      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);

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

04 August 2002, 18:48:33

[ New version of pg_proc.c used for application.]

Patch applied.  Thanks.  initdb forced.

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



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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

04 August 2002, 18:48:37

Got it and applied.  Thanks.  This is a major feature now.

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

Joe Conway wrote:
> Bruce Momjian wrote:
> > I am sorry but I am unable to apply this patch because of the DROP
> > COLUMN patch that was applied since you submitted this.
> >
> > It had rejections in gram.y and parse_relation.c, but those were easy to
> > fix.  The big problem is pg_proc.c, where the code changes can not be
> > merged.
> >
> > I am attaching the rejected part of the patch.  If you can send me a
> > fixed version of just that change, I can commit the rest.
> >
>
> OK. Here is a patch against current cvs for just pg_proc.c. This
> includes all the changes for that file (i.e. not just the one rejected
> hunk).
>
> Thanks,
>
> Joe
>
>

> Index: src/backend/catalog/pg_proc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/backend/catalog/pg_proc.c,v
> retrieving revision 1.82
> diff -c -r1.82 pg_proc.c
> *** src/backend/catalog/pg_proc.c    2 Aug 2002 18:15:05 -0000    1.82
> --- src/backend/catalog/pg_proc.c    4 Aug 2002 06:21:51 -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);
> ***************
> *** 367,460 ****
>        */
>       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 scan the non-deleted attributes to ensure
> !      * that they match the datatypes of the non-resjunk columns.
> !      */
> !     reln = heap_open(typerelid, AccessShareLock);
> !     relnatts = reln->rd_rel->relnatts;
> !     rellogcols = 0;                /* we'll count nondeleted cols as we go */
> !     colindex = 0;
> !
> !     foreach(tlistitem, tlist)
> !     {
> !         TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
> !         Form_pg_attribute attr;
> !         Oid            tletype;
> !         Oid            atttype;
>
> !         if (tle->resdom->resjunk)
> !             continue;
>
> !         do {
>               colindex++;
>               if (colindex > relnatts)
> !                 elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
> !                      format_type_be(rettype), rellogcols);
> !             attr = reln->rd_att->attrs[colindex - 1];
> !         } while (attr->attisdropped);
> !         rellogcols++;
> !
> !         tletype = exprType(tle->expr);
> !         atttype = attr->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),
> !                  rellogcols);
> !     }
> !
> !     for (;;)
> !     {
> !         colindex++;
> !         if (colindex > relnatts)
> !             break;
> !         if (!reln->rd_att->attrs[colindex - 1]->attisdropped)
> !             rellogcols++;
> !     }
>
> !     if (tlistlen != rellogcols)
> !         elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
> !              format_type_be(rettype), rellogcols);
>
> !     heap_close(reln, AccessShareLock);
>   }
>
>
> --- 368,480 ----
>        */
>       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 scan the non-deleted attributes to ensure
> +          * that they match the datatypes of the non-resjunk columns.
> +          */
> +         reln = heap_open(typerelid, AccessShareLock);
> +         relnatts = reln->rd_rel->relnatts;
> +         rellogcols = 0;                /* we'll count nondeleted cols as we go */
> +         colindex = 0;
>
> !         foreach(tlistitem, tlist)
> !         {
> !             TargetEntry *tle = (TargetEntry *) lfirst(tlistitem);
> !             Form_pg_attribute attr;
> !             Oid            tletype;
> !             Oid            atttype;
> !
> !             if (tle->resdom->resjunk)
> !                 continue;
> !
> !             do {
> !                 colindex++;
> !                 if (colindex > relnatts)
> !                     elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
> !                          format_type_be(rettype), rellogcols);
> !                 attr = reln->rd_att->attrs[colindex - 1];
> !             } while (attr->attisdropped);
> !             rellogcols++;
>
> !             tletype = exprType(tle->expr);
> !             atttype = attr->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),
> !                      rellogcols);
> !         }
>
> !         for (;;)
> !         {
>               colindex++;
>               if (colindex > relnatts)
> !                 break;
> !             if (!reln->rd_att->attrs[colindex - 1]->attisdropped)
> !                 rellogcols++;
> !         }
>
> !         if (tlistlen != rellogcols)
> !             elog(ERROR, "function declared to return %s does not SELECT the right number of columns (%d)",
> !                  format_type_be(rettype), rellogcols);
>
> !         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");
>   }
>
>
> ***************
> *** 553,558 ****
> --- 573,579 ----
>       bool        isnull;
>       Datum        tmp;
>       char       *prosrc;
> +     char        functyptype;
>
>       tuple = SearchSysCache(PROCOID, funcoid, 0, 0, 0);
>       if (!HeapTupleIsValid(tuple))
> ***************
> *** 569,576 ****
>
>       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);
> --- 590,600 ----
>
>       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);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go 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

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

04 August 2002, 18:55:34

Woh, seems like there is a compile problem.  Joe, my guess is that the
pg_proc patch you send me today didn't have _all_ of the needed changes.

I am attaching a patch with the fix need to get it to compile, but it is
clearly wrong.  Would you submit a fix based on current CVS for this?  I
know this gets confusing when patches conflict.


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

Bruce Momjian wrote:
>
> [ New version of pg_proc.c used for application.]
>
> Patch applied.  Thanks.  initdb forced.
>

--
  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
Index: src/backend/catalog/pg_proc.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/pg_proc.c,v
retrieving revision 1.83
diff -c -r1.83 pg_proc.c
*** src/backend/catalog/pg_proc.c    4 Aug 2002 19:48:09 -0000    1.83
--- src/backend/catalog/pg_proc.c    4 Aug 2002 19:53:42 -0000
***************
*** 318,324 ****
   * 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 /* XXX FIX ME */, List *queryTreeList)
  {
      Query       *parse;
      int            cmd;

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

04 August 2002, 18:59:38

Woh, again, I have another compile error:

    equalfuncs.c: In function `_equalRangeVar':
    equalfuncs.c:1610: structure has no member named `coldeflist'
    equalfuncs.c:1610: structure has no member named `coldeflist'
    gmake[2]: *** [equalfuncs.o] Error 1

Again, a patch is attached that clearly needs fixing.  I should have
tested this more, but I am heading out now and wanted to get it in
before more code drifted.

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

Bruce Momjian wrote:
>
> Woh, seems like there is a compile problem.  Joe, my guess is that the
> pg_proc patch you send me today didn't have _all_ of the needed changes.
>
> I am attaching a patch with the fix need to get it to compile, but it is
> clearly wrong.  Would you submit a fix based on current CVS for this?  I
> know this gets confusing when patches conflict.
>
>
> ---------------------------------------------------------------------------
>
> Bruce Momjian wrote:
> >
> > [ New version of pg_proc.c used for application.]
> >
> > Patch applied.  Thanks.  initdb forced.
> >

--
  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
Index: src/backend/catalog/pg_proc.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/pg_proc.c,v
retrieving revision 1.83
diff -c -r1.83 pg_proc.c
*** src/backend/catalog/pg_proc.c    4 Aug 2002 19:48:09 -0000    1.83
--- src/backend/catalog/pg_proc.c    4 Aug 2002 19:58:03 -0000
***************
*** 318,324 ****
   * 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 /* XXX FIX ME */, List *queryTreeList)
  {
      Query       *parse;
      int            cmd;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.147
diff -c -r1.147 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    4 Aug 2002 19:48:09 -0000    1.147
--- src/backend/nodes/equalfuncs.c    4 Aug 2002 19:58:03 -0000
***************
*** 1607,1615 ****
          return false;
      if (!equal(a->alias, b->alias))
          return false;
      if (!equal(a->coldeflist, b->coldeflist))
          return false;
!
      return true;
  }

--- 1607,1616 ----
          return false;
      if (!equal(a->alias, b->alias))
          return false;
+ /* FIX ME XXX
      if (!equal(a->coldeflist, b->coldeflist))
          return false;
! */
      return true;
  }

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

04 August 2002, 20:09:29

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Again, a patch is attached that clearly needs fixing.  I should have
> tested this more, but I am heading out now and wanted to get it in
> before more code drifted.

Would you mind backing it out, instead?  I've got major merge problems
now because I wasn't expecting that to be applied yet (it's still
completely unreviewed AFAIK).

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

04 August 2002, 20:47:35

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>>Again, a patch is attached that clearly needs fixing.  I should have
>>tested this more, but I am heading out now and wanted to get it in
>>before more code drifted.
>
>
> Would you mind backing it out, instead?  I've got major merge problems
> now because I wasn't expecting that to be applied yet (it's still
> completely unreviewed AFAIK).

I just got back -- sorry about the problem. By all means, back it out
and I'll work up a new patch based on current cvs.

Joe

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

04 August 2002, 23:27:04

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Again, a patch is attached that clearly needs fixing.  I should have
> > tested this more, but I am heading out now and wanted to get it in
> > before more code drifted.
>
> Would you mind backing it out, instead?  I've got major merge problems
> now because I wasn't expecting that to be applied yet (it's still
> completely unreviewed AFAIK).

I can back it out, but it may cause the same problems when I try to
apply it later.  It was submitted July 28, added to the patch queue
August 1, and applied today, August 4.  I don't remember anyone saying
they wanted to review it.  It is an extension to an earlier patch.

If we back it out and delay it more, won't the patch become even harder
to apply?  Let me know.  (It was actually your DROP COLUMN commit that
made it hard to apply yesterday.)

One trick I use for patch problems is this: If a patch doesn't apply,
and it is too hard to manually merge, I generate a diff via CVS of the
last change to the file, save it, reverse out that diff, then apply the
rejected part of the new patch, _then_ apply the CVS diff I generated.
In many cases, the rejections of that last CVS patch _will_ be able to
be manually applied, i.e. if a patch changes 100 lines, but a previous
patch changed 3 lines, you can back out the 3-line change, apply the
100-line change, then manually patch in the 3-line change based on the
new contents of the file.

Let me know what you want me to do.

--
  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

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

04 August 2002, 23:42:20

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can back it out, but it may cause the same problems when I try to
> apply it later.  It was submitted July 28, added to the patch queue
> August 1, and applied today, August 4.  I don't remember anyone saying
> they wanted to review it.  It is an extension to an earlier patch.

Let me just put down a marker now: anything that large I want to review.

I'm currently trying to deal with the fact that the patch broke
functions that return domain types.  For example:

regression=# create function foo() returns setof int as
regression-# 'select unique1 from tenk1 limit 10' language sql;
CREATE FUNCTION
regression=# create domain myint as int;
CREATE DOMAIN
regression=# create function myfoo() returns setof myint as
regression-# 'select unique1 from tenk1 limit 10' language sql;
ERROR:  Unknown kind of return type specified for function

The alias hacking seems to have a few holes as well:

regression=# select  * from foo();
 foo
------
 8800
 1891
 3420
 ...

(fine)

regression=# select  * from foo() as z;
 foo
------
 8800
 1891
 3420
 9850
 ...

(hm, what happened to the alias?)

regression=# select  * from foo() as z(a int);
 foo
------
 8800
 1891
 3420
 9850
 7164
 ...

(again, what happened to the alias?  Column name should be a)

regression=# select  * from foo() as z(a int8);
 foo
------
 8800
 1891
 3420
 9850

(definitely not cool)

> If we back it out and delay it more, won't the patch become even harder
> to apply?  Let me know.

Right now the last thing we need is more CVS churn.  Let's see if it can
be fixed.  Joe, you need to reconsider the relationship between alias
clauses and this feature, and also reconsider checking of the types.

> One trick I use for patch problems is this: If a patch doesn't apply,
> and it is too hard to manually merge, I generate a diff via CVS of the
> last change to the file, save it, reverse out that diff, then apply the
> rejected part of the new patch, _then_ apply the CVS diff I
> generated.

It'd be kind of nice if you actually tested the result.  Once again
you've applied a large patch without bothering to see if it compiled,
let alone passed regression.

Yes, I'm feeling annoyed this evening...

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

04 August 2002, 23:48:35

Tom Lane wrote:
> Right now the last thing we need is more CVS churn.  Let's see if it can
> be fixed.  Joe, you need to reconsider the relationship between alias
> clauses and this feature, and also reconsider checking of the types.

OK, I'm on it now. Sorry I missed those issues. I guess my own testing
was too myopic :(

Joe

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

05 August 2002, 00:10:36

Tom Lane wrote:
> It'd be kind of nice if you actually tested the result.  Once again
> you've applied a large patch without bothering to see if it compiled,
> let alone passed regression.
>
> Yes, I'm feeling annoyed this evening...

Yes, I hear you.  I don't normally work on Sunday afternoon, especially
when I am heading out, but Joe's patch had sat too long already that the
code had drifted so I thought I would get it in ASAP.

Sorry it caused you problems.

--
  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

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

05 August 2002, 01:06:05

Joe Conway <mail@joeconway.com> writes:
> OK, I'm on it now. Sorry I missed those issues. I guess my own testing
> was too myopic :(

Okay.  I have patches to fix the domain-type issues, and will commit
as soon as I've finished testing 'em.

I would suggest that either gram.y or someplace early in the analyzer
should transpose the names from the coldeflist into the "user specified
alias" structure.  That should fix the alias naming issues.  The other
issues indicate that if a coldeflist is provided, you should check it
against the function return type in all cases not only RECORD.  In the
non-RECORD cases it could be done in the parse analysis phase.

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 01:11:03

Tom Lane wrote:
> Okay.  I have patches to fix the domain-type issues, and will commit
> as soon as I've finished testing 'em.

Thanks.

>
> I would suggest that either gram.y or someplace early in the analyzer
> should transpose the names from the coldeflist into the "user specified
> alias" structure.  That should fix the alias naming issues.  The other
> issues indicate that if a coldeflist is provided, you should check it
> against the function return type in all cases not only RECORD.  In the
> non-RECORD cases it could be done in the parse analysis phase.

Actually, I was just looking at this and remembering that I wanted to
disallow a coldeflist for non-RECORD return types. Do you prefer to
allow it (but properly apply the alias and enforce the type)?

Joe

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

05 August 2002, 01:59:32

Joe Conway <mail@joeconway.com> writes:
> Actually, I was just looking at this and remembering that I wanted to
> disallow a coldeflist for non-RECORD return types. Do you prefer to
> allow it (but properly apply the alias and enforce the type)?

We could do that too; what I was unhappy about was that the system
took the syntax and then didn't apply the type checking that the syntax
seems to imply.  I'd prefer to do the type checking ... but I don't
want to expend a heckuva lot of code on it, so maybe erroring out
is the better answer.

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 03:01:13

The attached patch disallows the use of coldeflists for functions that
don't return type RECORD. It also catches a core dump condition when a
function returning RECORD had an alias list instead of a coldeflist.

Now both conditions throw an ERROR.

Sample output below:

Tom Lane wrote:
> regression=# select  * from foo() as z;
>  foo
> ------
>  8800
>  1891
>  3420
>  9850
>  ...
>
> (hm, what happened to the alias?)

Actually nothing wrong with this one. The z is the relation alias, not
the column alias. The column alias defaults to the function name for
SRFs returning scalar. If you try:

test=# select myfoo1.* from myfoo1() as z;
ERROR:  Relation "myfoo1" does not exist

which is as expected.

>
> regression=# select  * from foo() as z(a int);
>  foo
> ------
>  8800
>  1891
>  3420
>  9850
>  7164
>  ...
>
> (again, what happened to the alias?  Column name should be a)

This one now throws an error:
test=# select * from myfoo1() as z(a int);
ERROR:  A column definition list is only allowed for functions returning
RECORD


>
> regression=# select  * from foo() as z(a int8);
>  foo
> ------
>  8800
>  1891
>  3420
>  9850
>
> (definitely not cool)

Same here.

Other change is like so:
test=# create function myfoo2() returns setof record as 'select * from
ct limit 10' language sql;

test=# select * from myfoo2() as z(a);
ERROR:  A column definition list is required for functions returning RECORD
test=# select * from myfoo2();
ERROR:  A column definition list is required for functions returning RECORD
test=# select * from myfoo2() as (a int, b text, c text, d text, e text);
  a  |   b    |   c   |  d   |  e
----+--------+-------+------+------
   1 | group1 | test1 | att1 | val1
   2 | group1 | test1 | att2 | val2
   3 | group1 | test1 | att3 | val3
   4 | group1 | test1 | att4 | val4
   5 | group1 | test2 | att1 | val5
   6 | group1 | test2 | att2 | val6
   7 | group1 | test2 | att3 | val7
   8 | group1 | test2 | att4 | val8
   9 | group2 | test3 | att1 | val1
  10 | group2 | test3 | att2 | val2
(10 rows)

test=# select * from myfoo2() as (a int8, b text, c text, d text, e text);
ERROR:  Query-specified return tuple and actual function return tuple do
not match


Please apply if no objections.

Thanks,

Joe
Index: src/backend/parser/parse_relation.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
retrieving revision 1.73
diff -c -r1.73 parse_relation.c
*** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
--- src/backend/parser/parse_relation.c    5 Aug 2002 03:16:42 -0000
***************
*** 729,734 ****
--- 729,755 ----
       */
      functyptype = get_typtype(funcrettype);

+     if (coldeflist != NIL)
+     {
+         /*
+          * we *only* allow a coldeflist for functions returning a
+          * RECORD pseudo-type
+          */
+         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))
+             elog(ERROR, "A column definition list is only allowed for"
+                         " functions returning RECORD");
+     }
+     else
+     {
+         /*
+          * ... and a coldeflist is *required* for functions returning a
+          * RECORD pseudo-type
+          */
+         if (functyptype == 'p' && funcrettype == RECORDOID)
+             elog(ERROR, "A column definition list is required for functions"
+                         " returning RECORD");
+     }
+
      if (functyptype == 'c')
      {
          /*

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

05 August 2002, 03:09:24

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> regression=# select  * from foo() as z;
>> foo
>> ------
>> 8800
>> ...
>>
>> (hm, what happened to the alias?)

> Actually nothing wrong with this one. The z is the relation alias, not
> the column alias. The column alias defaults to the function name for
> SRFs returning scalar.

Hm.  I'd sort of expect the "z" to become both the table and column
alias in this case.  What do you think?

Other examples look good.  Code style comment:

> +         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))

This test seems redundant, why not

        if (functyptype != 'p' || funcrettype != RECORDOID)

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

"Christopher Kings-Lynne"

Date:

05 August 2002, 03:09:59

> test=# select * from myfoo2() as (a int8, b text, c text, d text, e text);
> ERROR:  Query-specified return tuple and actual function return tuple do
> not match

I wonder if that would read a little better (and perhaps be in the active
voice) if it was like below.  The word 'actual' seems a little casual, and
do people really know what tuples are?

ERROR: Query-specified function result alias does not match defined function
result type.

Chris

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 03:20:06

Tom Lane wrote:
> Hm.  I'd sort of expect the "z" to become both the table and column
> alias in this case.  What do you think?

I guess that would make sense. I'll make a separate patch just for that
change if that's OK.

> Other examples look good.  Code style comment:
>
>
>>+         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))
>
>
> This test seems redundant, why not
>
>         if (functyptype != 'p' || funcrettype != RECORDOID)
>

You're correct, of course. But I started out with a much more convoluted
test than that, so at least this was an improvement ;-)

Joe

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 04:58:15

Joe Conway wrote:
> Tom Lane wrote:
>> Hm.  I'd sort of expect the "z" to become both the table and column
>> alias in this case.  What do you think?
>
> I guess that would make sense. I'll make a separate patch just for that
> change if that's OK.
>

Simple change -- patch attached.

test=# select * from myfoo1() as z;
  z
----
   1
   2
   3
(3 rows)

test=# select * from myfoo1();
  myfoo1
--------
       1
       2
       3
(3 rows)


test=# select * from myfoo1() as z(a);
  a
----
   1
   2
   3
(3 rows)


Joe
Index: src/backend/parser/parse_relation.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
retrieving revision 1.73
diff -c -r1.73 parse_relation.c
*** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
--- src/backend/parser/parse_relation.c    5 Aug 2002 05:22:02 -0000
***************
*** 807,813 ****
              elog(ERROR, "Too many column aliases specified for function %s",
                   funcname);
          if (numaliases == 0)
!             eref->colnames = makeList1(makeString(funcname));
      }
      else if (functyptype == 'p' && funcrettype == RECORDOID)
      {
--- 807,813 ----
              elog(ERROR, "Too many column aliases specified for function %s",
                   funcname);
          if (numaliases == 0)
!             eref->colnames = makeList1(makeString(eref->aliasname));
      }
      else if (functyptype == 'p' && funcrettype == RECORDOID)
      {

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 05:20:24

Joe Conway wrote:
> Simple change -- patch attached.

Of course, the simple change has ripple effects! Here's a patch for the
rangefunc regression test for the new behavior.

Joe
Index: src/test/regress/expected/rangefuncs.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v
retrieving revision 1.2
diff -c -r1.2 rangefuncs.out
*** src/test/regress/expected/rangefuncs.out    16 Jul 2002 05:53:34 -0000    1.2
--- src/test/regress/expected/rangefuncs.out    5 Aug 2002 05:52:01 -0000
***************
*** 48,56 ****
  -- sql, proretset = f, prorettype = b
  CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  getfoo
! --------
!       1
  (1 row)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
--- 48,56 ----
  -- sql, proretset = f, prorettype = b
  CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  t1
! ----
!   1
  (1 row)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
***************
*** 65,74 ****
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  getfoo
! --------
!       1
!       1
  (2 rows)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
--- 65,74 ----
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  t1
! ----
!   1
!   1
  (2 rows)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
***************
*** 84,91 ****
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  getfoo
! --------
   Joe
   Ed
  (2 rows)
--- 84,91 ----
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
  SELECT * FROM getfoo(1) AS t1;
!  t1
! -----
   Joe
   Ed
  (2 rows)
***************
*** 139,147 ****
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE 'plpgsql'; 
  SELECT * FROM getfoo(1) AS t1;
!  getfoo
! --------
!       1
  (1 row)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
--- 139,147 ----
  DROP FUNCTION getfoo(int);
  CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE 'plpgsql'; 
  SELECT * FROM getfoo(1) AS t1;
!  t1
! ----
!   1
  (1 row)

  CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

Re: anonymous composite types for Table Functions (aka SRFs)

From

Tom Lane

Date:

05 August 2002, 12:37:55

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Hm.  I'd sort of expect the "z" to become both the table and column
>> alias in this case.  What do you think?

> I guess that would make sense. I'll make a separate patch just for that
> change if that's OK.

In the cold light of morning I started to wonder what should happen if
you write "from foo() as z" when foo returns a tuple.  It would probably
be peculiar for the z to overwrite the column name of just the first
column --- there is no such column renaming for an ordinary table alias.

My current thought: z becomes the table alias, and it also becomes the
column alias *if* the function returns scalar.  For a function returning
tuple, this syntax doesn't affect the column names.  (In any case this
syntax is disallowed for functions returning RECORD.)

            regards, tom lane

Re: anonymous composite types for Table Functions (aka

From

Joe Conway

Date:

05 August 2002, 14:13:15

Tom Lane wrote:
> In the cold light of morning I started to wonder what should happen if
> you write "from foo() as z" when foo returns a tuple.  It would probably
> be peculiar for the z to overwrite the column name of just the first
> column --- there is no such column renaming for an ordinary table alias.
>
> My current thought: z becomes the table alias, and it also becomes the
> column alias *if* the function returns scalar.  For a function returning
> tuple, this syntax doesn't affect the column names.  (In any case this
> syntax is disallowed for functions returning RECORD.)

I think the one liner patch I sent in last night does exactly what you
describe -- so I guess we're in complete agreement ;-)

See below:

test=# \d foo
          Table "foo"
  Column |  Type   | Modifiers
--------+---------+-----------
  f1     | bytea   |
  f2     | integer |
Indexes: foo_idx1 btree (f1)

test=# create function foo1() returns setof int as 'select f2 from foo'
language sql;
CREATE FUNCTION
test=# create function foo2() returns setof foo as 'select * from foo'
language sql;
CREATE FUNCTION
test=# select * from foo1() as z where z.z = 1;
  z
---
  1
(1 row)

test=# select * from foo1() as z(a) where z.a = 1;
  a
---
  1
(1 row)

test=# select * from foo2() as z where z.f2 = 1;
            f1           | f2
------------------------+----
  \237M@y[J\272z\304\003 |  1
(1 row)

test=# select * from foo2() as z(a) where z.f2 = 1;
            a            | f2
------------------------+----
  \237M@y[J\272z\304\003 |  1
(1 row)

test=# create function foo3() returns setof record as 'select * from
foo' language sql;
CREATE FUNCTION
test=# select * from foo3() as z where z.f2 = 1;
ERROR:  A column definition list is required for functions returning RECORD
test=# select * from foo3() as z(a bytea, b int) where z.f2 = 1;
ERROR:  No such attribute z.f2
test=# select * from foo3() as z(a bytea, b int) where z.b = 1;
            a            | b
------------------------+---
  \237M@y[J\272z\304\003 | 1
(1 row)


Joe

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

05 August 2002, 15:23:04

I assume this is the patch you and Tom now want applied.

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:
> The attached patch disallows the use of coldeflists for functions that
> don't return type RECORD. It also catches a core dump condition when a
> function returning RECORD had an alias list instead of a coldeflist.
>
> Now both conditions throw an ERROR.
>
> Sample output below:
>
> Tom Lane wrote:
> > regression=# select  * from foo() as z;
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  ...
> >
> > (hm, what happened to the alias?)
>
> Actually nothing wrong with this one. The z is the relation alias, not
> the column alias. The column alias defaults to the function name for
> SRFs returning scalar. If you try:
>
> test=# select myfoo1.* from myfoo1() as z;
> ERROR:  Relation "myfoo1" does not exist
>
> which is as expected.
>
> >
> > regression=# select  * from foo() as z(a int);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  7164
> >  ...
> >
> > (again, what happened to the alias?  Column name should be a)
>
> This one now throws an error:
> test=# select * from myfoo1() as z(a int);
> ERROR:  A column definition list is only allowed for functions returning
> RECORD
>
>
> >
> > regression=# select  * from foo() as z(a int8);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >
> > (definitely not cool)
>
> Same here.
>
> Other change is like so:
> test=# create function myfoo2() returns setof record as 'select * from
> ct limit 10' language sql;
>
> test=# select * from myfoo2() as z(a);
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2();
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2() as (a int, b text, c text, d text, e text);
>   a  |   b    |   c   |  d   |  e
> ----+--------+-------+------+------
>    1 | group1 | test1 | att1 | val1
>    2 | group1 | test1 | att2 | val2
>    3 | group1 | test1 | att3 | val3
>    4 | group1 | test1 | att4 | val4
>    5 | group1 | test2 | att1 | val5
>    6 | group1 | test2 | att2 | val6
>    7 | group1 | test2 | att3 | val7
>    8 | group1 | test2 | att4 | val8
>    9 | group2 | test3 | att1 | val1
>   10 | group2 | test3 | att2 | val2
> (10 rows)
>
> test=# select * from myfoo2() as (a int8, b text, c text, d text, e text);
> ERROR:  Query-specified return tuple and actual function return tuple do
> not match
>
>
> Please apply if no objections.
>
> Thanks,
>
> Joe

> Index: src/backend/parser/parse_relation.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
> retrieving revision 1.73
> diff -c -r1.73 parse_relation.c
> *** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
> --- src/backend/parser/parse_relation.c    5 Aug 2002 03:16:42 -0000
> ***************
> *** 729,734 ****
> --- 729,755 ----
>        */
>       functyptype = get_typtype(funcrettype);
>
> +     if (coldeflist != NIL)
> +     {
> +         /*
> +          * we *only* allow a coldeflist for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))
> +             elog(ERROR, "A column definition list is only allowed for"
> +                         " functions returning RECORD");
> +     }
> +     else
> +     {
> +         /*
> +          * ... and a coldeflist is *required* for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype == 'p' && funcrettype == RECORDOID)
> +             elog(ERROR, "A column definition list is required for functions"
> +                         " returning RECORD");
> +     }
> +
>       if (functyptype == 'c')
>       {
>           /*

--
  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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

05 August 2002, 15:25:21

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:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Hm.  I'd sort of expect the "z" to become both the table and column
> >> alias in this case.  What do you think?
> >
> > I guess that would make sense. I'll make a separate patch just for that
> > change if that's OK.
> >
>
> Simple change -- patch attached.
>
> test=# select * from myfoo1() as z;
>   z
> ----
>    1
>    2
>    3
> (3 rows)
>
> test=# select * from myfoo1();
>   myfoo1
> --------
>        1
>        2
>        3
> (3 rows)
>
>
> test=# select * from myfoo1() as z(a);
>   a
> ----
>    1
>    2
>    3
> (3 rows)
>
>
> Joe

> Index: src/backend/parser/parse_relation.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
> retrieving revision 1.73
> diff -c -r1.73 parse_relation.c
> *** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
> --- src/backend/parser/parse_relation.c    5 Aug 2002 05:22:02 -0000
> ***************
> *** 807,813 ****
>               elog(ERROR, "Too many column aliases specified for function %s",
>                    funcname);
>           if (numaliases == 0)
> !             eref->colnames = makeList1(makeString(funcname));
>       }
>       else if (functyptype == 'p' && funcrettype == RECORDOID)
>       {
> --- 807,813 ----
>               elog(ERROR, "Too many column aliases specified for function %s",
>                    funcname);
>           if (numaliases == 0)
> !             eref->colnames = makeList1(makeString(eref->aliasname));
>       }
>       else if (functyptype == 'p' && funcrettype == RECORDOID)
>       {

--
  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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

05 August 2002, 15:25:38

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:
> Joe Conway wrote:
> > Simple change -- patch attached.
>
> Of course, the simple change has ripple effects! Here's a patch for the
> rangefunc regression test for the new behavior.
>
> Joe

> Index: src/test/regress/expected/rangefuncs.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v
> retrieving revision 1.2
> diff -c -r1.2 rangefuncs.out
> *** src/test/regress/expected/rangefuncs.out    16 Jul 2002 05:53:34 -0000    1.2
> --- src/test/regress/expected/rangefuncs.out    5 Aug 2002 05:52:01 -0000
> ***************
> *** 48,56 ****
>   -- sql, proretset = f, prorettype = b
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 48,56 ----
>   -- sql, proretset = f, prorettype = b
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> ***************
> *** 65,74 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
> !       1
>   (2 rows)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 65,74 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
> !   1
>   (2 rows)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> ***************
> *** 84,91 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
>    Joe
>    Ed
>   (2 rows)
> --- 84,91 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! -----
>    Joe
>    Ed
>   (2 rows)
> ***************
> *** 139,147 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE 'plpgsql'; 
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 139,147 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE 'plpgsql'; 
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

>
> ---------------------------(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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

05 August 2002, 15:28:07

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:
> The attached patch disallows the use of coldeflists for functions that
> don't return type RECORD. It also catches a core dump condition when a
> function returning RECORD had an alias list instead of a coldeflist.
>
> Now both conditions throw an ERROR.
>
> Sample output below:
>
> Tom Lane wrote:
> > regression=# select  * from foo() as z;
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  ...
> >
> > (hm, what happened to the alias?)
>
> Actually nothing wrong with this one. The z is the relation alias, not
> the column alias. The column alias defaults to the function name for
> SRFs returning scalar. If you try:
>
> test=# select myfoo1.* from myfoo1() as z;
> ERROR:  Relation "myfoo1" does not exist
>
> which is as expected.
>
> >
> > regression=# select  * from foo() as z(a int);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  7164
> >  ...
> >
> > (again, what happened to the alias?  Column name should be a)
>
> This one now throws an error:
> test=# select * from myfoo1() as z(a int);
> ERROR:  A column definition list is only allowed for functions returning
> RECORD
>
>
> >
> > regression=# select  * from foo() as z(a int8);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >
> > (definitely not cool)
>
> Same here.
>
> Other change is like so:
> test=# create function myfoo2() returns setof record as 'select * from
> ct limit 10' language sql;
>
> test=# select * from myfoo2() as z(a);
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2();
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2() as (a int, b text, c text, d text, e text);
>   a  |   b    |   c   |  d   |  e
> ----+--------+-------+------+------
>    1 | group1 | test1 | att1 | val1
>    2 | group1 | test1 | att2 | val2
>    3 | group1 | test1 | att3 | val3
>    4 | group1 | test1 | att4 | val4
>    5 | group1 | test2 | att1 | val5
>    6 | group1 | test2 | att2 | val6
>    7 | group1 | test2 | att3 | val7
>    8 | group1 | test2 | att4 | val8
>    9 | group2 | test3 | att1 | val1
>   10 | group2 | test3 | att2 | val2
> (10 rows)
>
> test=# select * from myfoo2() as (a int8, b text, c text, d text, e text);
> ERROR:  Query-specified return tuple and actual function return tuple do
> not match
>
>
> Please apply if no objections.
>
> Thanks,
>
> Joe

> Index: src/backend/parser/parse_relation.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
> retrieving revision 1.73
> diff -c -r1.73 parse_relation.c
> *** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
> --- src/backend/parser/parse_relation.c    5 Aug 2002 03:16:42 -0000
> ***************
> *** 729,734 ****
> --- 729,755 ----
>        */
>       functyptype = get_typtype(funcrettype);
>
> +     if (coldeflist != NIL)
> +     {
> +         /*
> +          * we *only* allow a coldeflist for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))
> +             elog(ERROR, "A column definition list is only allowed for"
> +                         " functions returning RECORD");
> +     }
> +     else
> +     {
> +         /*
> +          * ... and a coldeflist is *required* for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype == 'p' && funcrettype == RECORDOID)
> +             elog(ERROR, "A column definition list is required for functions"
> +                         " returning RECORD");
> +     }
> +
>       if (functyptype == 'c')
>       {
>           /*

>
> ---------------------------(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

Re: anonymous composite types for Table Functions (aka SRFs)

From

Bruce Momjian

Date:

06 August 2002, 04:33:42

Patch applied.  Thanks.

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



Joe Conway wrote:
> The attached patch disallows the use of coldeflists for functions that
> don't return type RECORD. It also catches a core dump condition when a
> function returning RECORD had an alias list instead of a coldeflist.
>
> Now both conditions throw an ERROR.
>
> Sample output below:
>
> Tom Lane wrote:
> > regression=# select  * from foo() as z;
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  ...
> >
> > (hm, what happened to the alias?)
>
> Actually nothing wrong with this one. The z is the relation alias, not
> the column alias. The column alias defaults to the function name for
> SRFs returning scalar. If you try:
>
> test=# select myfoo1.* from myfoo1() as z;
> ERROR:  Relation "myfoo1" does not exist
>
> which is as expected.
>
> >
> > regression=# select  * from foo() as z(a int);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >  7164
> >  ...
> >
> > (again, what happened to the alias?  Column name should be a)
>
> This one now throws an error:
> test=# select * from myfoo1() as z(a int);
> ERROR:  A column definition list is only allowed for functions returning
> RECORD
>
>
> >
> > regression=# select  * from foo() as z(a int8);
> >  foo
> > ------
> >  8800
> >  1891
> >  3420
> >  9850
> >
> > (definitely not cool)
>
> Same here.
>
> Other change is like so:
> test=# create function myfoo2() returns setof record as 'select * from
> ct limit 10' language sql;
>
> test=# select * from myfoo2() as z(a);
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2();
> ERROR:  A column definition list is required for functions returning RECORD
> test=# select * from myfoo2() as (a int, b text, c text, d text, e text);
>   a  |   b    |   c   |  d   |  e
> ----+--------+-------+------+------
>    1 | group1 | test1 | att1 | val1
>    2 | group1 | test1 | att2 | val2
>    3 | group1 | test1 | att3 | val3
>    4 | group1 | test1 | att4 | val4
>    5 | group1 | test2 | att1 | val5
>    6 | group1 | test2 | att2 | val6
>    7 | group1 | test2 | att3 | val7
>    8 | group1 | test2 | att4 | val8
>    9 | group2 | test3 | att1 | val1
>   10 | group2 | test3 | att2 | val2
> (10 rows)
>
> test=# select * from myfoo2() as (a int8, b text, c text, d text, e text);
> ERROR:  Query-specified return tuple and actual function return tuple do
> not match
>
>
> Please apply if no objections.
>
> Thanks,
>
> Joe

> Index: src/backend/parser/parse_relation.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
> retrieving revision 1.73
> diff -c -r1.73 parse_relation.c
> *** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
> --- src/backend/parser/parse_relation.c    5 Aug 2002 03:16:42 -0000
> ***************
> *** 729,734 ****
> --- 729,755 ----
>        */
>       functyptype = get_typtype(funcrettype);
>
> +     if (coldeflist != NIL)
> +     {
> +         /*
> +          * we *only* allow a coldeflist for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype != 'p' || (functyptype == 'p' && funcrettype != RECORDOID))
> +             elog(ERROR, "A column definition list is only allowed for"
> +                         " functions returning RECORD");
> +     }
> +     else
> +     {
> +         /*
> +          * ... and a coldeflist is *required* for functions returning a
> +          * RECORD pseudo-type
> +          */
> +         if (functyptype == 'p' && funcrettype == RECORDOID)
> +             elog(ERROR, "A column definition list is required for functions"
> +                         " returning RECORD");
> +     }
> +
>       if (functyptype == 'c')
>       {
>           /*

--
  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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

06 August 2002, 04:34:20

Patch applied.  Thanks.

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


Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Hm.  I'd sort of expect the "z" to become both the table and column
> >> alias in this case.  What do you think?
> >
> > I guess that would make sense. I'll make a separate patch just for that
> > change if that's OK.
> >
>
> Simple change -- patch attached.
>
> test=# select * from myfoo1() as z;
>   z
> ----
>    1
>    2
>    3
> (3 rows)
>
> test=# select * from myfoo1();
>   myfoo1
> --------
>        1
>        2
>        3
> (3 rows)
>
>
> test=# select * from myfoo1() as z(a);
>   a
> ----
>    1
>    2
>    3
> (3 rows)
>
>
> Joe

> Index: src/backend/parser/parse_relation.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_relation.c,v
> retrieving revision 1.73
> diff -c -r1.73 parse_relation.c
> *** src/backend/parser/parse_relation.c    5 Aug 2002 02:30:50 -0000    1.73
> --- src/backend/parser/parse_relation.c    5 Aug 2002 05:22:02 -0000
> ***************
> *** 807,813 ****
>               elog(ERROR, "Too many column aliases specified for function %s",
>                    funcname);
>           if (numaliases == 0)
> !             eref->colnames = makeList1(makeString(funcname));
>       }
>       else if (functyptype == 'p' && funcrettype == RECORDOID)
>       {
> --- 807,813 ----
>               elog(ERROR, "Too many column aliases specified for function %s",
>                    funcname);
>           if (numaliases == 0)
> !             eref->colnames = makeList1(makeString(eref->aliasname));
>       }
>       else if (functyptype == 'p' && funcrettype == RECORDOID)
>       {

--
  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

Re: anonymous composite types for Table Functions (aka

From

Bruce Momjian

Date:

06 August 2002, 04:34:55

Patch applied.  Thanks.

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



Joe Conway wrote:
> Joe Conway wrote:
> > Simple change -- patch attached.
>
> Of course, the simple change has ripple effects! Here's a patch for the
> rangefunc regression test for the new behavior.
>
> Joe

> Index: src/test/regress/expected/rangefuncs.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v
> retrieving revision 1.2
> diff -c -r1.2 rangefuncs.out
> *** src/test/regress/expected/rangefuncs.out    16 Jul 2002 05:53:34 -0000    1.2
> --- src/test/regress/expected/rangefuncs.out    5 Aug 2002 05:52:01 -0000
> ***************
> *** 48,56 ****
>   -- sql, proretset = f, prorettype = b
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 48,56 ----
>   -- sql, proretset = f, prorettype = b
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> ***************
> *** 65,74 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
> !       1
>   (2 rows)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 65,74 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
> !   1
>   (2 rows)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> ***************
> *** 84,91 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
>    Joe
>    Ed
>   (2 rows)
> --- 84,91 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! -----
>    Joe
>    Ed
>   (2 rows)
> ***************
> *** 139,147 ****
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE 'plpgsql'; 
>   SELECT * FROM getfoo(1) AS t1;
> !  getfoo
> ! --------
> !       1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
> --- 139,147 ----
>   DROP FUNCTION getfoo(int);
>   CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE 'plpgsql'; 
>   SELECT * FROM getfoo(1) AS t1;
> !  t1
> ! ----
> !   1
>   (1 row)
>
>   CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

>
> ---------------------------(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