Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT

On 08/27/2015 04:49 AM, Christopher BROWN wrote:
> Hello,
>
> I'm new to this list but have been using PostgreSQL for a moment. I've
> encountered an error using PostgreSQL 9.4.4 which can be reproduced
> using the SQL below.
>
> The trigger "init_store_ldap_profiles_trigger" fails if the function
> "init_store_ldap_profiles()" is written as below. If I rewrite it to
> use "SELECT * FROM ...", instead of "SELECT id, ref_ldap_department,
> ref_ldap_title, access_mode FROM ...", it works.
>
> This is the error I get:
> ERROR: null value in column "access_mode" violates not-null constraint
> Detail: Failing row contains (1, 2015-08-27 13:37:24.306883,
> 2015-08-27 13:37:24.306883, 1, 1, 1, null).
> Where: SQL statement "INSERT INTO application.store_ldap_profile
> (ref_store, ref_ldap_department, ref_ldap_title, access_mode) VALUES
> (NEW.id, r.ref_ldap_department, r.ref_ldap_title, r.access_mode)"
> PL/pgSQL function init_store_ldap_profiles() line 8 at SQL statement
>
> It seems that for some reason, the column
> "store_ldap_profile_defaults.access_mode" appears to be NULL when
> referred to using r.access_mode (r being the declared %ROWTYPE). I can
> modify the WHERE clause to add a dummy condition on "access_mode", and
> that works (as in, it doesn't solve my problem but the column value is
> visible to the WHERE clause).
>
> Is this a bug or can I fix this in my SQL ?

It is not a bug, see below for more.

>
> Thanks,
> Christopher
>
> Here's the SQL :

> CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$
> DECLARE
> r application.store_ldap_profile_defaults%rowtype;

Per Charles's post the ROWTYPE is tripping you up.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

"A variable of a composite type is called a row variable (or row-type
variable). Such a variable can hold a whole row of a SELECT or FOR query
result, so long as that query's column set matches the declared type of
the variable. The individual fields of the row value are accessed using
the usual dot notation, for example rowvar.field."

You are selecting one less field then the ROWTYPE declared type, so
access_mode(the extra field in the ROWTYPE) is set to NULL. It works
when you do * because then the query column count matches the ROWTYPE
column count.

So the choices are:

1) Use ROWTYPE and select all the columns

2) Use RECORD, which adapts itself to the columns returned:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS

> BEGIN
> FOR r IN
> SELECT id, ref_ldap_department, ref_ldap_title, access_mode FROM
> application.store_ldap_profile_defaults WHERE format = NEW.format
> LOOP
> INSERT INTO application.store_ldap_profile (ref_store,
> ref_ldap_department, ref_ldap_title, access_mode) VALUES (NEW.id,
> r.ref_ldap_department, r.ref_ldap_title, r.access_mode);
> END LOOP;
> RETURN NEW;
> END; $$
> LANGUAGE plpgsql VOLATILE;
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com