Thread: set-returning function in pg 7.4.6

Hi,

I need to write a function returning a set of integer.
I'm basing this example on GetRows from http://techdocs.postgresql.org/guides/SetReturningFunctions

Here's the function:

create or replace function GetRows() returns setof int as
'
declare
    r record;
begin
    for r in EXECUTE ''select customer_id from customers'' loop
        return next r.customer_id;
    end loop;
    return;
end
'
language 'plpgsql';


When I call this function as

 select GetRows();

I get this error:

ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "getrows" line 5 at return next



What am I doing wrong?

Raph

On Mon, 14 Mar 2005, Raphael Bauduin wrote:

> Here's the function:
>
> create or replace function GetRows() returns setof int as
> '
> declare
>     r record;
> begin
>     for r in EXECUTE ''select customer_id from customers'' loop
>         return next r.customer_id;
>     end loop;
>     return;
> end
> '
> language 'plpgsql';
>
>
> When I call this function as
>
>  select GetRows();
>
> I get this error:
>
> ERROR:  set-valued function called in context that cannot accept a set
> CONTEXT:  PL/pgSQL function "getrows" line 5 at return next

You need to call it with the function in the from clause
 select * from GetRows();

On Mon, Mar 14, 2005 at 04:50:38PM +0100, Raphael Bauduin wrote:

>    for r in EXECUTE ''select customer_id from customers'' loop

If the query is static (i.e., not generated) and you're not working
with a temporary table, then you can omit EXECUTE:

  FOR r IN SELECT customer_id FROM customers LOOP

The function might benefit from using a cached plan in this case.

> select GetRows();
>
> I get this error:
>
> ERROR:  set-valued function called in context that cannot accept a set
> CONTEXT:  PL/pgSQL function "getrows" line 5 at return next

It should be:

SELECT * FROM GetRows();

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/