From: | Jean-Christophe Boggio <cat(at)thefreecat(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jason Davis <jdavis(at)tassie(dot)net(dot)au>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re[2]: SETOF modifier |
Date: | 2000-10-16 15:33:41 |
Message-ID: | 1634988300.20001016173341@thefreecat.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Tom,
Ref : Monday, October 16, 2000 6:39:48 AM
TL> Jason Davis <jdavis(at)tassie(dot)net(dot)au> writes:
>> I have been trying to create a basic SQL function which returns a SETOF
>> values, without much luck. The docs make plenty of mention of the fact you
>> can return multiple values from a function, but unfortunately don't give
>> any examples as such. The syntax I thought would work is along the lines of
>> CREATE FUNCTION sp_testing() RETURNS setof text AS '
>> SELECT col1, col2, col3 FROM table;
>> ' LANGUAGE 'sql';
TL> 'setof' implies that the function can return multiple *rows*, not
TL> multiple columns. The error message you're getting is not real helpful
TL> in existing releases --- you see 'function declared to return text
TL> returns multiple values in final retrieve', right? (The fact that it
TL> says RETRIEVE not SELECT betrays the age of this code...) For 7.1 I've
TL> reworded it as 'function declared to return text returns multiple
TL> columns in final SELECT', which may be less confusing.
TL> If you want to merge the results of three columns across all rows in
TL> "table" into one undifferentiated result, a possible way is
TL> CREATE FUNCTION sp_testing() RETURNS setof text AS '
TL> SELECT col1 FROM table UNION ALL
TL> SELECT col2 FROM table UNION ALL
TL> SELECT col3 FROM table;
TL> ' LANGUAGE 'sql';
And how do you get the effective results ?
select sp_testing();
does not work.
Where can I find documentation about :
* returning multiple rows from a plpgsql function (if possible) ?
* returning multiple values from a plpgsql function ?
Thanks a LOT !
--
Jean-Christophe Boggio
cat(at)thefreecat(dot)org
Independant Consultant and Developer
Delphi, Linux, Oracle, Perl
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-10-16 15:44:30 | Re: Re[2]: SETOF modifier |
Previous Message | 이재훈 | 2000-10-16 05:57:53 | thanks.. |