Обсуждение: calling a function that takes a row type and returns a set of rows
So, say I have something like this - the actual example is something a bit more useful: CREATE TABLE foo (a integer, b integer); INSERT INTO foo VALUES (1, 1); -- must have some data to generate the failure CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$ DECLARE f foo; BEGIN f.a := 1; RETURN NEXT f; f.a := 2; RETURN NEXT f; END $$ LANGUAGE plpgsql; I can't find any legal way of calling this function. SELECT bar(f) FROM foo f; ERROR: set-valued function called in context that cannot accept a set SELECT * FROM foo f, bar(f); ERROR: function expression in FROM may not refer to other relations of same query level Any help appreciated. Thanks, ...Robert
Hello PostgreSQL doesn't support pipe functions, so you cannot do what you wont. But you should to use SQL SETOF functions, that should be called in normal context. I dislike this feature, but it should be useful for you, try: create or replace function bar1(foo) returns setof foo as $$ select 1, $1.b union all select 2, $1.b; $$ language sql; postgres=# select (bar1(foo)).* from foo; a | b ---+--- 1 | 1 2 | 1 (2 rows) I thing, so much better and cleaner version is using explicit or implicit cursor in function -- implicit cursor create or replace function bar() returns setof foo as $$ declare r record; begin for r in select * from foo loop r.a := 1; return next r; r.a := 2; return next r; end loop; return; end; $$ language plpgsql; postgres=# select * from bar(); a | b ---+--- 1 | 1 2 | 1 (2 rows) -- using explicit cursor (it's more complicated variant, and I thing, so it's better don't use it) create or replace function bar(c refcursor) returns setof foo as $$ declare r record; begin loop fetch c into r; exit when not found; r.a := 1; return next r; r.a := 2; return next r; end loop; return; end; $$ language plpgsql; begin; declare x cursor for select * from foo; select * from bar('x'::refcursor); commit; postgres=# declare x cursor for select * from foo; DECLARE CURSOR postgres=# select * from bar('x'::refcursor); a | b ---+--- 1 | 1 2 | 1 (2 rows) postgres=# commit; COMMIT Regards Pavel Stehule 2008/10/10 Robert Haas <robertmhaas@gmail.com>: > So, say I have something like this - the actual example is something a > bit more useful: > > CREATE TABLE foo (a integer, b integer); > INSERT INTO foo VALUES (1, 1); -- must have some data to generate the failure > > CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$ > DECLARE > f foo; > BEGIN > f.a := 1; > RETURN NEXT f; > f.a := 2; > RETURN NEXT f; > END > $$ LANGUAGE plpgsql; > > I can't find any legal way of calling this function. > > SELECT bar(f) FROM foo f; > ERROR: set-valued function called in context that cannot accept a set > > SELECT * FROM foo f, bar(f); > ERROR: function expression in FROM may not refer to other relations > of same query level > > Any help appreciated. > > Thanks, > > ...Robert > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hmm, the implicit cursor approach approach won't work for me because I want to be able to call the function on an arbitrary slice of the rows in the table, but the explicit cursor approach looks like it might work. I'll give that a try, thanks. ...Robert On Fri, Oct 10, 2008 at 4:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > PostgreSQL doesn't support pipe functions, so you cannot do what you > wont. But you should to use SQL SETOF functions, that should be > called in normal context. I dislike this feature, but it should be > useful for you, > > try: > > create or replace function bar1(foo) > returns setof foo as $$ > select 1, $1.b > union all > select 2, $1.b; > $$ language sql; > > postgres=# select (bar1(foo)).* from foo; > a | b > ---+--- > 1 | 1 > 2 | 1 > (2 rows) > > I thing, so much better and cleaner version is using explicit or > implicit cursor in function > > -- implicit cursor > create or replace function bar() returns setof foo as $$ > declare r record; > begin > for r in select * from foo loop > r.a := 1; > return next r; > r.a := 2; > return next r; > end loop; > return; > end; > $$ language plpgsql; > > postgres=# select * from bar(); > a | b > ---+--- > 1 | 1 > 2 | 1 > (2 rows) > > -- using explicit cursor (it's more complicated variant, and I thing, > so it's better don't use it) > create or replace function bar(c refcursor) returns setof foo as $$ > declare r record; > begin > loop > fetch c into r; > exit when not found; > r.a := 1; > return next r; > r.a := 2; > return next r; > end loop; > return; > end; > $$ language plpgsql; > > begin; > declare x cursor for select * from foo; > select * from bar('x'::refcursor); > commit; > > postgres=# declare x cursor for select * from foo; > DECLARE CURSOR > postgres=# select * from bar('x'::refcursor); > a | b > ---+--- > 1 | 1 > 2 | 1 > (2 rows) > > postgres=# commit; > COMMIT > > Regards > Pavel Stehule > > > 2008/10/10 Robert Haas <robertmhaas@gmail.com>: >> So, say I have something like this - the actual example is something a >> bit more useful: >> >> CREATE TABLE foo (a integer, b integer); >> INSERT INTO foo VALUES (1, 1); -- must have some data to generate the failure >> >> CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$ >> DECLARE >> f foo; >> BEGIN >> f.a := 1; >> RETURN NEXT f; >> f.a := 2; >> RETURN NEXT f; >> END >> $$ LANGUAGE plpgsql; >> >> I can't find any legal way of calling this function. >> >> SELECT bar(f) FROM foo f; >> ERROR: set-valued function called in context that cannot accept a set >> >> SELECT * FROM foo f, bar(f); >> ERROR: function expression in FROM may not refer to other relations >> of same query level >> >> Any help appreciated. >> >> Thanks, >> >> ...Robert >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Le 10 oct. 08 à 21:22, Robert Haas a écrit : > I can't find any legal way of calling this function. > > SELECT bar(f) FROM foo f; > ERROR: set-valued function called in context that cannot accept a set > > SELECT * FROM foo f, bar(f); > ERROR: function expression in FROM may not refer to other relations > of same query level > > Any help appreciated. You need LATERAL support for this: SELECT * FROM foo f LATERAL bar(f); I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein upper "nodes" are visible. - -- dim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx fbMAoK31dHoFjOVRdomvhl/qilndRZJ5 =3xjL -----END PGP SIGNATURE-----
> You need LATERAL support for this: > SELECT * FROM foo f LATERAL bar(f); > > I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein > upper "nodes" are visible. That would be really nice. Then you could presumably also do: SELECT f.id, f.name, f.apple, f.banana, bar.apple AS bar_apple, bar.banana AS bar_banana FROM foo f LATERAL bar(f); ...which I frequently wish to do, and can't. ...Robert
2008/10/10 Dimitri Fontaine <dfontaine@hi-media.com>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > Le 10 oct. 08 à 21:22, Robert Haas a écrit : >> >> I can't find any legal way of calling this function. >> >> SELECT bar(f) FROM foo f; >> ERROR: set-valued function called in context that cannot accept a set >> >> SELECT * FROM foo f, bar(f); >> ERROR: function expression in FROM may not refer to other relations >> of same query level >> >> Any help appreciated. > > > You need LATERAL support for this: > SELECT * FROM foo f LATERAL bar(f); > > I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein > upper "nodes" are visible. > - -- > dim > no, this strange syntax is far to any standard. Solution is using dynamic cursor ala DB2 (that isn't supported in postgres) - select * from fce(cursor(select .... from tab)) Regards Pavel Stehule > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (Darwin) > > iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx > fbMAoK31dHoFjOVRdomvhl/qilndRZJ5 > =3xjL > -----END PGP SIGNATURE----- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Le 11 oct. 08 à 08:18, Pavel Stehule a écrit : >> I'm not sure about the syntax, but LATERAL is a standard JOIN type >> wherein >> upper "nodes" are visible. > no, this strange syntax is far to any standard. Solution is using > dynamic cursor ala DB2 (that isn't supported in postgres) - select * > from fce(cursor(select .... from tab)) Yes it's standard notation. SQL2008 has it in Foundation document, see its grammar reference at page 343 of my PDF reader, labelled page 321 (Query Expressions): 7.6 <table reference> <table primary> ::= ... | <lateral derived table> [ AS ] <correlation name> [ <left paren><derived column list><right paren> ] ... <lateral derived table> ::= LATERAL<table subquery> You'll find out more from http://wiscorp.com/SQLStandards.html Regards, - -- dim -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkjxByYACgkQlBXRlnbh1blh8wCeNpirQ5902oVgH8Xh8rhYr3qF rOMAn1opkDflbRn9PPuD1fmMGblLvzgQ =7wSp -----END PGP SIGNATURE-----