Re: anonymous composite types for Table Functions (aka

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