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