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

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