From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Kynn Jones <kynnjo(at)gmail(dot)com> |
Cc: | "pgsql-general General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: "RETURNS SETOF" function question |
Date: | 2008-02-25 00:08:01 |
Message-ID: | 26B4246C-4FF9-4CFC-8B49-8858C835B79A@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)myemma(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-02-25 02:01:08 | Re: no-arg cluster and locks ... |
Previous Message | Scott Marlowe | 2008-02-24 23:32:03 | Re: reindexing |