Hi Gunnar,
Thanks for reply.
I found when the problem appears.
When the procedure is defined like this problem appears:
Code:
CREATE OR REPLACE FUNCTION aquirenotifications(pnotification_type character, papp_code character, pexcluded_app_codes text, pmax_results integer)
RETURNS SETOF notification_vo AS
$BODY$
DECLARE
results_row notification_vo%rowtype;
BEGIN
for results_row in select n.notification_id, n.notification_type, n.app_code, n.notification_app_id, n.recipient, n.subject, n.content, n.status, n.channel_code, n.mime_type, n.created, c.channel_definition
from notification n, channel c
where notification_type = pnotification_type
and n.channel_code = c.channel_code
and (app_code = papp_code or papp_code is null)
and ( not app_code = ANY( string_to_array(pexcluded_app_codes, ',')) or pexcluded_app_codes is null)
and status = 1 order by created asc limit pmax_results
loop
results_row.status = 2;
return next results_row;
update notification set status = 2 where notification_id = results_row.notification_id;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
but when I use cursor as a return object it works:
Code:
CREATE OR REPLACE FUNCTION aquirenotifications2(pnotification_type character, papp_code character, pexcluded_app_codes text, pmax_results integer)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT n.notification_id, n.notification_type, n.app_code, n.notification_app_id, n.recipient, n.subject, n.content, n.status, n.channel_code, n.mime_type, n.created, c.channel_definition
from notification n, channel c
where notification_type = pnotification_type
and n.channel_code = c.channel_code
and (app_code = papp_code or papp_code is null)
and ( not app_code = ANY( string_to_array(pexcluded_app_codes, ',')) or pexcluded_app_codes is null)
and status = 1 order by created asc limit pmax_results;
loop
--update notification set status = 2 from ref where notification_id = ref.notification_id;
return next ref;
end loop;
close ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
I would like to use the first version of the procedure becasue I would like to do updates inside loop.
I the first procedure result parameter apperas as input parameter during the procedure call.
What could be the problem?
Wojciech
stored-proc-inbound-channel-adapter wit returning-resultset returning SqlOutParameter
Reply With Quote