Re: SETOF modifier

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';

'setof' implies that the function can return multiple *rows*, not
multiple columns. The error message you're getting is not real helpful
in existing releases --- you see 'function declared to return text
returns multiple values in final retrieve', right? (The fact that it
says RETRIEVE not SELECT betrays the age of this code...) For 7.1 I've
reworded it as 'function declared to return text returns multiple
columns in final SELECT', which may be less confusing.

If you want to merge the results of three columns across all rows in
"table" into one undifferentiated result, a possible way is

CREATE FUNCTION sp_testing() RETURNS setof text AS '
SELECT col1 FROM table UNION ALL
SELECT col2 FROM table UNION ALL
SELECT col3 FROM table;
' LANGUAGE 'sql';

regards, tom lane