plpgsql: UPDATE...Returning in FOR loop
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
------------------------------------------------------------------
"Raymond O'Donnell" <rod@iol.ie> writes:
I'm wondering if it's possible to use UPDATE...RETURNING, instead of
SELECT, in a FOR loop like this:
...
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
Well, that has nothing to do with UPDATE RETURNING; it's apparently
failing here:
rec recipients;
I suppose "recipients" is a composite type one of whose columns is of a
NOT NULL domain. Best advice is "don't do that" --- not-null domains
were not one of the SQL committee's better ideas. If you're really in
love with your existing schema, though, you could probably work around
it by declaring rec as "record" instead of the specific composite type.
regards, tom lane
On Mon, Mar 02, 2009 at 07:34:55PM +0000, Raymond O'Donnell wrote:
when I try it I get the following error:
I've never tried doing things like this before, but it looks as though
everything is working. I'd interpret your error message:
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
as saying that you've defined "message_type_domain" as used below:
TheType message_type_domain,
as something like:
CREATE DOMAIN message_type_domain AS TEXT NOT NULL;
And when the function starts up it initializes the "TheType" variable
to be NULL, and this promptly fails the constraint check. Tee hee, fun
problem. Easiest fix would appear to be to use the base type instead
of the domain type (I think that would work anyway) or to initialize the
variable to something other than NULL. The real fix goes a bit deeper
into PG's knowledge of types and SQL's mixing up of Option types.
--
Sam http://samason.me.uk/
On 02/03/2009 20:19, Tom Lane wrote:
Well, that has nothing to do with UPDATE RETURNING; it's apparently
failing here:rec recipients;
I suppose "recipients" is a composite type one of whose columns is of a
NOT NULL domain. Best advice is "don't do that" --- not-null domains
Thanks Tom and Sam for your replies - I hadn't absorbed the bit in the
docs about variables being initialised to NULL in the absence of
anything else.... removing NOT NULL from message_type_domain did the
trick, and all works as I hoped.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------