Thread: Confused about a function returning SETOF

Confused about a function returning SETOF

From
"Ken Winter"
Date:

I have the following function:

 

CREATE or REPLACE FUNCTION public.lov_personinorganization_role_status(

varchar,

varchar)

RETURNS SETOF person_status_in_organization AS

$BODY$

DECLARE

    rec person_status_in_organization%ROWTYPE;

    role ALIAS FOR $1;

    from_status ALIAS FOR $2;

BEGIN

    FOR rec IN

        SELECT * FROM person_status_in_organization lov,

            status_in_organization_change_lov res

        WHERE res.role = role

            AND res.from_status = from_status

            AND lov.role = res.role

            AND lov.status = res.to_status

        ORDER BY lov.display_order

    LOOP

        RETURN NEXT rec;

    END LOOP; -- FOR rec IN person_status_in_organization

    RETURN;

END; -- of lov_personinorganization_role_status

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

 

Where person_status_in_organization is a table.

 

When I try to invoke it with:

 

select lov_personinorganization_role_status('Student','Applicant');

 

I get the error message:

 

ERROR:  set-valued function called in context that cannot accept a set

CONTEXT:  PL/pgSQL function "lov_personinorganization_role_status" line 15 at return next

 

Where have I gone astray?

 

~ TIA

~ Ken

Re: Confused about a function returning SETOF

From
Tom Lane
Date:
"Ken Winter" <ken@sunward.org> writes:
> select lov_personinorganization_role_status('Student','Applicant');
> ERROR:  set-valued function called in context that cannot accept a set

You need to write
  select * from lov_personinorganization_role_status('Student','Applicant');

            regards, tom lane

Re: Confused about a function returning SETOF

From
"Ken Winter"
Date:
Well, duh!  Thank you.  I could have stared at it for hours without seeing
it...

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, March 21, 2006 11:12 PM
> To: ken@sunward.org
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] Confused about a function returning SETOF
>
> "Ken Winter" <ken@sunward.org> writes:
> > select lov_personinorganization_role_status('Student','Applicant');
> > ERROR:  set-valued function called in context that cannot accept a set
>
> You need to write
>   select * from
> lov_personinorganization_role_status('Student','Applicant');
>
>             regards, tom lane