Thread: plpgsql: UPDATE...Returning in FOR loop

plpgsql: UPDATE...Returning in FOR loop

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: plpgsql: UPDATE...Returning in FOR loop

From
Tom Lane
Date:
"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

Re: plpgsql: UPDATE...Returning in FOR loop

From
Sam Mason
Date:
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/

Re: plpgsql: UPDATE...Returning in FOR loop

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

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




Re: plpgsql: UPDATE...Returning in FOR loop

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

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