Thread: "RETURNS SETOF" function question

On Feb 24, 2008, at 4:19 PM, Kynn Jones wrote:

>
> Suppose that stored procedure foo has the signature:
>
>   foo( text, text ) RETURNS SETOF text
>
> Also, I have some table bar, and that column bar.baz is of type text.
>
> Now, I'd like to run something like
>
>   SELECT foo( "frobozz", baz ) FROM bar;
>
> If I try this psql complains that I'm trying to execute a set-
> valued function in the wrong context.
>
> But the intention of this invalid statement is to apply foo
> ( "frobozz", ? ) once for each row of bar, replacing ? each time
> with the row's value of baz, and concatenate all the returned
> tables to produce the final result.  (In general, the number of
> rows resulting from this application has no relation to the number
> of rows in bar; i.e. it can be less than, equal to, or greater than
> this number.)
>
> What must I do to get the desired behavior?

There was an article that covered this in the Postgres Online Journal
(http://www.postgresonline.com/journal/index.php?/categories/6-pl-
programming).  Basically, do this:

CREATE OR REPLACE FUNCTION foo(text, text)
    RETURNS SETOF text
$$
    SELECT * FROM foo($1, $2);
$$
LANGUAGE sql;

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com