Re: anonymous composite types for Table Functions (aka

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