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

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/