Thread: http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
I can't work out from that how to return more than one value. -Nigel
> I can't work out from that how to return more than one value.
Hi Nigel,
Add SETOF to your function like so:
CREATE TABLE test (id int);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
CREATE FUNCTION test_func() RETURNS SETOF integer AS '
SELECT id FROM test;
' LANGUAGE SQL;
SELECT test_func();
Cheers
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: > I can't work out from that how to return more than one value. 17:35 < rtfm_please> For information about srf 17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions 17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: > am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: > > I can't work out from that how to return more than one value. > > 17:35 < rtfm_please> For information about srf > 17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions > 17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 How does that help with my problem? I seems to discuss returning more than one row of a table which is not the question I asked. > Regards, Andreas -Nigel
Nigel Horne schrieb: > On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: > >>am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: >> >>>I can't work out from that how to return more than one value. >> >>17:35 < rtfm_please> For information about srf >>17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions >>17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 > > > How does that help with my problem? I seems to discuss returning more > than one row of a table which is not the question I asked. > try to tell your questions more precisely :-) I think you want to return a record or tabletype. IIrc you got the answers to that already :-)
On Mon, 2005-08-22 at 14:49, Tino Wildenhain wrote: > Nigel Horne schrieb: > > On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: > > > >>am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: > >> > >>>I can't work out from that how to return more than one value. > >> > >>17:35 < rtfm_please> For information about srf > >>17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions > >>17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 > > > > > > How does that help with my problem? I seems to discuss returning more > > than one row of a table which is not the question I asked. > > > > try to tell your questions more precisely :-) I want to return more than one value from a procedure, e.g. a string and an integer. > I think you want to return a record or tabletype. Not really, since those values could be computed on the fly, they may not be values in a database. > IIrc you got the answers to that already :-) Nope.
am 22.08.2005, um 14:56:09 +0100 mailte Nigel Horne folgendes: > > > How does that help with my problem? I seems to discuss returning more > > > than one row of a table which is not the question I asked. > > > > > > > try to tell your questions more precisely :-) > > I want to return more than one value from a procedure, e.g. a string and > an integer. > > > I think you want to return a record or tabletype. > > Not really, since those values could be computed on the fly, they may > not be values in a database. No. A record is a record, not a database nor table. > > > IIrc you got the answers to that already :-) > > Nope. Read again. Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On 8/22/05 9:56 AM, "Nigel Horne" <njh@bandsman.co.uk> wrote: > On Mon, 2005-08-22 at 14:49, Tino Wildenhain wrote: >> Nigel Horne schrieb: >>> On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: >>> >>>> am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: >>>> >>>>> I can't work out from that how to return more than one value. >>>> >>>> 17:35 < rtfm_please> For information about srf >>>> 17:35 < rtfm_please> see >>>> http://techdocs.postgresql.org/guides/SetReturningFunctions >>>> 17:35 < rtfm_please> or >>>> http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 >>> >>> >>> How does that help with my problem? I seems to discuss returning more >>> than one row of a table which is not the question I asked. >>> >> >> try to tell your questions more precisely :-) > > I want to return more than one value from a procedure, e.g. a string and > an integer. > >> I think you want to return a record or tabletype. > > Not really, since those values could be computed on the fly, they may > not be values in a database. Actually, that is what you want. Here is a concrete example: CREATE OR REPLACE FUNCTION test_return(int,int) RETURNS RECORD AS $$ DECLARE a alias for $1; b alias for $2; ret record; BEGIN select into ret a, b, a+b; RETURN ret; END; $$ language plpgsql; select * from test_return(1,2) as t(a int, b int, s int); a | b | s ---+---+--- 1 | 2 | 3 (1 row)
On 22/8/05 2:56 pm, "Nigel Horne" <njh@bandsman.co.uk> wrote: > On Mon, 2005-08-22 at 14:49, Tino Wildenhain wrote: >> Nigel Horne schrieb: >>> On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: >>> >>>> am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: >>>> >>>>> I can't work out from that how to return more than one value. >>>> >>>> 17:35 < rtfm_please> For information about srf >>>> 17:35 < rtfm_please> see >>>> http://techdocs.postgresql.org/guides/SetReturningFunctions >>>> 17:35 < rtfm_please> or >>>> http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 >>> >>> >>> How does that help with my problem? I seems to discuss returning more >>> than one row of a table which is not the question I asked. >>> >> >> try to tell your questions more precisely :-) > > I want to return more than one value from a procedure, e.g. a string and > an integer. > >> I think you want to return a record or tabletype. > > Not really, since those values could be computed on the fly, they may > not be values in a database. > >> IIrc you got the answers to that already :-) > > Nope. Hi Nigel, Well if you have not yet received the answer that you require, then you probably haven't asked your question properly.... Because several people have answered the question as you stated it. You might have to be more specific about your requirements to get a proper answer. Cheers Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Nigel Horne schrieb: > On Mon, 2005-08-22 at 14:49, Tino Wildenhain wrote: > >>Nigel Horne schrieb: >> >>>On Fri, 2005-08-19 at 16:34, A. Kretschmer wrote: >>> >>> >>>>am 19.08.2005, um 15:58:20 +0100 mailte Nigel Horne folgendes: >>>> >>>> >>>>>I can't work out from that how to return more than one value. >>>> >>>>17:35 < rtfm_please> For information about srf >>>>17:35 < rtfm_please> see http://techdocs.postgresql.org/guides/SetReturningFunctions >>>>17:35 < rtfm_please> or http://www.postgresql.org/docs/current/static/xfunc-sql.html#AEN28835 >>> >>> >>>How does that help with my problem? I seems to discuss returning more >>>than one row of a table which is not the question I asked. >>> >> >>try to tell your questions more precisely :-) > > > I want to return more than one value from a procedure, e.g. a string and > an integer. > > >>I think you want to return a record or tabletype. > > > Not really, since those values could be computed on the fly, they may > not be values in a database. > > >>IIrc you got the answers to that already :-) > > > Nope. Well, I was sure. And here it is again (from Tom Lane: ) --- cite --- You've misunderstood this completely. We are not storing anything essential in the table, we're just using its rowtype to describe the function's composite-type result. Personally I would have written the example using a composite type to make this more clear: CREATE TYPE test_func_type AS (id int, name text); CREATE FUNCTION test_func() RETURNS SETOF test_func_type AS $$ SELECT 1, 'me' UNION ALL SELECT 2, 'you' $$ LANGUAGE sql; select * from test_func(); id | name ----+------ 1 | me 2 | you (2 rows) --- cite --- When this isnt what you want, you are out of options I fear. Maybe you would write an example of how your hypotetical function should act? Regards Tino