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