Обсуждение: SELECT syntax synopsis: column_definition?
Can any one give an example of the difference between a column_alias and a column_definition when using a function in the FROM clause? from the manual: http://www.postgresql.org/docs/8.2/interactive/sql-select.html "function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]" Regards, Richard Broersma Jr.
On Aug 21, 2007, at 14:34 , Richard Broersma Jr wrote: > Can any one give an example of the difference between a > column_alias and a column_definition when > using a function in the FROM clause? > > from the manual: > http://www.postgresql.org/docs/8.2/interactive/sql-select.html > > "function_name ( [ argument [, ...] ] ) [ AS ] alias > [ ( column_alias [, ...] | column_definition > [, ...] ) ]" I believe it's similar to this # select * from generate_series(1,10) as a(s); s ---- 1 2 3 4 5 6 7 8 9 10 (10 rows) But like this: # select * from generate_series(1,10) as a(s text); But not quite, as this raises an error :) ERROR: a column definition list is only allowed for functions returning "record" So the *form* is right, but I don't know of an example that works. You've got me curious now, too! Michael Glaesemann grzm seespotcode net
On Aug 21, 2007, at 18:04 , Michael Glaesemann wrote: > So the *form* is right, but I don't know of an example that works. CREATE TABLE foos ( foo text PRIMARY KEY , title text NOT NULL ); INSERT INTO foos (foo, title) values ('foo', 'the great') , ('bar', 'the extravagant') , ('baz', 'the indisputable'); CREATE OR REPLACE FUNCTION get_foo() RETURNS record LANGUAGE plpgsql AS $body$ DECLARE v_record record; BEGIN SELECT INTO v_record * FROM foos ORDER BY RANDOM() LIMIT 1; RETURN v_record; END; $body$; a | b -----+------------------ baz | the indisputable (1 row) test=# SELECT * test-# FROM get_foo() AS (a text, b text); a | b -----+----------------- bar | the extravagant (1 row) IIRC, this form is used by the crosstab functions in tablefunc. Michael Glaesemann grzm seespotcode net
"Michael Glaesemann" <grzm@seespotcode.net> writes: > ERROR: a column definition list is only allowed for functions returning > "record" > > So the *form* is right, but I don't know of an example that works. postgres=# create function testf() returns record as 'select 1' language sql; CREATE FUNCTION postgres=# select * from testf() as (i integer);i ---1 (1 row) I haven't quite figured out how this is useful though. It probably makes more sense if you use plpgsql but I still don't quite see what the use case is. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On 8/21/07, Gregory Stark <stark@enterprisedb.com> wrote: > "Michael Glaesemann" <grzm@seespotcode.net> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create function testf() returns record as 'select 1' language sql; > CREATE FUNCTION > postgres=# select * from testf() as (i integer); > i > --- > 1 > (1 row) > > > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. I use them with the crosstab function in the tablefunc contrib module.
Gregory Stark wrote: > "Michael Glaesemann" <grzm@seespotcode.net> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create function testf() returns record as 'select 1' language sql; > CREATE FUNCTION > postgres=# select * from testf() as (i integer); > i > --- > 1 > (1 row) > > > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. Any function declared as returning SETOF RECORD needs it, when you don't use OUT params. Before OUT params existed, it was the only way to use those functions. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" "The eagle never lost so much time, as when he submitted to learn of the crow." (William Blake)
--- Alvaro Herrera <alvherre@commandprompt.com> wrote: > Any function declared as returning SETOF RECORD needs it, when you don't > use OUT params. Before OUT params existed, it was the only way to use > those functions. Thanks everyone for the exposition! It makes sense. Regards, Richard Broersma Jr.
Gregory Stark <stark@enterprisedb.com> writes: > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. IIRC, the case that actually convinced people to allow it was dblink. You want to be able to do something like select * from dblink('select a,b,c from remote_table') as (a int, b text, c float8); The declaration of dblink can't be any more specific than "RETURNS SETOF RECORD", so there's no help to be had there. The only way to explain to the parser what your dblink call is going to return is something like the above. And the parser does need to know it, so it knows what to expand "*" to (or more generally, to do things like joins involving the rowset result). regards, tom lane