Thread: 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