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(at)postgresql(dot)org)

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