plpgsql: UPDATE...Returning in FOR loop

["Raymond O'Donnell" <rod@xxxxxx>]

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@xxxxxx
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general