Skip site navigation (1) Skip section navigation (2)

Re: SETOF modifier

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jason Davis <jdavis(at)tassie(dot)net(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SETOF modifier
Date: 2000-10-16 04:39:48
Message-ID: 21747.971671188@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: 이재훈Date: 2000-10-16 05:57:53
Subject: thanks..
Previous:From: Jason DavisDate: 2000-10-16 04:04:35
Subject: SETOF modifier

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group