Re: [GENERAL] "RETURNS SETOF" function question

[Kynn Jones]

On Sun, Feb 24, 2008 at 7:08 PM, Erik Jones <[EMAIL PROTECTED]> wrote:

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


That one goes straight into my (still tiny) bag o' Pg tricks.  It worked
like a charm.

Thanks!

G.

Re: [GENERAL] "RETURNS SETOF" function question

[Erik Jones]

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®
[EMAIL PROTECTED]
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




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] "RETURNS SETOF" function question

[Kynn Jones]

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?

TIA!

kynn