Обсуждение: Confused about a function returning SETOF
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
"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
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