Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
От | Adrian Klaver |
---|---|
Тема | Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT |
Дата | |
Msg-id | 55DF0FD1.5040005@aklaver.com обсуждение исходный текст |
Ответ на | Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT (Christopher BROWN <brown@reflexe.fr>) |
Ответы |
Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
(Christopher BROWN <brown@reflexe.fr>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Christopher BROWNДата:
Сообщение: Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT
Следующее
От: Christopher BROWNДата:
Сообщение: Re: Strange TRIGGER failure with FOR ... IN ... LOOP ... INSERT