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

Hello Adrian,

Yep, Charles' explanation helped me understand what was going on. Before
that, I was as confused as you were (in your first reply) about how
access_mode could be NULL (with the same reasoning). In any case, thanks
for your links ; I did try searching the web for the answer before posting,
but got too many irrelevant results given that I had to search using very
common terms.

I've concluded the the RECORD type is the best-fit for my approach. I
don't know if it's any faster that using SELECT * with a specific %ROWTYPE
given that the data doesn't go anywhere outside the function body. I don't
know if the order in which columns are returned (by either SELECT * or
using explicit column names matters when using %ROWTYPE), although I'll
assume that PostgreSQL is smart enough to match things up correctly, if I
need to write a function that returns instances of any given %ROWTYPE in
the future.

Thanks again.
Christopher

On 27 August 2015 at 15:25, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> 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
>