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...
Show quoted text
-----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 setYou need to write
select * from
lov_personinorganization_role_status('Student','Applicant');regards, tom lane