Re[2]: SETOF modifier

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