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

"Raymond O'Donnell" <rod(at)iol(dot)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