Hi all,
I'm wondering if it's possible to use UPDATE...RETURNING, instead of
SELECT, in a FOR loop like this:
for rec in
update recipients set batch_id = TheID where recip_id = any (
select recip_id from recipients where msg_id = TheMessage
and recip_type = TheType and batch_id = -1 limit TheBatchSize
) returning recip_id, recip_type, msg_id, delivery_address,
fullname, batch_id
loop
return next rec;
end loop;
The function and table definitions are given below.
I'm guessing that this isn't possible, because when I try it I get the
following error:
gti_messaging=> select recipients_for_delivery(5, 'Email', 20);
ERROR: domain message_type_domain does not allow null values
CONTEXT: PL/pgSQL function "recipients_for_delivery" line 4 during
statement block local variable initialization
Here's the full function definition:
create or replace function recipients_for_delivery(
TheMessage integer,
TheType message_type_domain,
TheBatchSize integer
) returns setof recipients
as
$$
declare
TheID integer;
rec recipients;
begin
-- Get the new batch ID.
select nextval('batches_batch_id_seq'::regclass) into TheID;
insert into batches(batch_id, delivery_succeeded, delivery_message)
values(TheID, false, '');
for rec in
update recipients set batch_id = TheID where recip_id = any (
select recip_id from recipients where msg_id = TheMessage
and recip_type = TheType and batch_id = -1 limit TheBatchSize
) returning recip_id, recip_type, msg_id, delivery_address,
fullname, batch_id
loop
return next rec;
end loop;
return;
end;
$$
language plpgsql;
And the "recipients" table is simply:
CREATE TABLE recipients
(
recip_id serial NOT NULL,
recip_type message_type_domain NOT NULL DEFAULT ('Email'::character
varying)::message_type_domain,
msg_id integer NOT NULL,
delivery_address character varying(120) NOT NULL,
fullname character varying(80) NOT NULL,
batch_id integer NOT NULL DEFAULT (-1),
CONSTRAINT recipients_pk PRIMARY KEY (recip_id),
CONSTRAINT message_fk FOREIGN KEY (msg_id)
REFERENCES messages (msg_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
Many thanks.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------