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.klaver@aklaver.com