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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
