Hello Charles, Your first suggestion effectively matches what I did as a workaround, with the advantage that your suggestion explicitly orders the columns and has an explanation attached... and your second suggestion seems to match what I was originally trying to do.
I was, perhaps naively, trying to select only the columns I needed, trying to avoid "SELECT *" as it's "considered harmful"... ; maybe in this context I don't need to worry..? Getting back to your second suggestion, it would seem that declaring "r RECORD" is a very good solution, because it also has the advantage of future-proofing function code against changes to the declared tablename%rowtype (no column suddenly becomes null due to structural changes). Would that be a reasonable assertion? It would in that case seem that trying to strongly-type the variable does more harm than good in this context. As a side note, I was also able to eliminate returning the "id" column value too, as a result of declaring "r" as "RECORD". I originally added it there to avoid an error message complaining about "r cannot be converted to an integer" (or something like that), but I've figured why I got that message too now, based on your explanation. Thanks, Christopher On 27 August 2015 at 15:01, Charles Clavadetscher < clavadetsc...@swisspug.org> wrote: > Another possibility is > > > > CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$ > > DECLARE > > r RECORD; > > 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; > > > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Charles Clavadetscher > *Sent:* Donnerstag, 27. August 2015 14:57 > *To:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP > ... INSERT > > > > Hello > > > > You declare your variable r as of type > application.store_ldap_profile_defaults%rowtype, but then select only 4 of > the 5 fields of the table to put in there. The last one (happens to be > access_mode is then null). > > The structures don’t match. That may explain this behaviour. > > > > This works: > > > > CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$ > > DECLARE > > r application.store_ldap_profile_defaults%rowtype; > > BEGIN > > FOR r IN > > SELECT id, ref_ldap_department, ref_ldap_title, format, > 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; > > > > Bye > > Charles > > > > *From:* pgsql-general-ow...@postgresql.org [ > mailto:pgsql-general-ow...@postgresql.org > <pgsql-general-ow...@postgresql.org>] *On Behalf Of *Christopher BROWN > *Sent:* Donnerstag, 27. August 2015 13:50 > *To:* pgsql-general@postgresql.org > *Subject:* [GENERAL] Strange TRIGGER failure with FOR ... IN ... LOOP ... > INSERT > > > > 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 ? > > > > Thanks, > > Christopher > > > > Here's the SQL : > > > > > > CREATE SCHEMA application; > > SET search_path TO application; > > > > CREATE TABLE IF NOT EXISTS store ( > > id SERIAL PRIMARY KEY, > > ctime TIMESTAMP NOT NULL DEFAULT now(), > > mtime TIMESTAMP NOT NULL DEFAULT now(), > > is_archived NUMERIC(1) CHECK (is_archived IN (1,0)) > DEFAULT 0, > > name VARCHAR(200) NOT NULL CHECK (length(name) > 0), > > hrcompany VARCHAR(200) NOT NULL CHECK > (length(hrcompany) > 0), > > hrsite VARCHAR(200) NOT NULL CHECK (length(hrsite) > > 0), > > format VARCHAR(1) NOT NULL CHECK (format IN ('H', > 'S')), > > UNIQUE (hrcompany, hrsite) > > ); > > > > CREATE INDEX ON store (mtime); > > CREATE INDEX ON store (is_archived); > > CREATE INDEX ON store (format); > > > > > > CREATE TABLE IF NOT EXISTS ldap_department ( > > id SERIAL PRIMARY KEY, > > ctime TIMESTAMP NOT NULL DEFAULT now(), > > mtime TIMESTAMP NOT NULL DEFAULT now(), > > code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'), > > label VARCHAR(200) NOT NULL CHECK (length(label) > > 0), > > UNIQUE(code) > > ); > > > > CREATE INDEX ON ldap_department (mtime); > > > > > > CREATE TABLE IF NOT EXISTS ldap_title ( > > id SERIAL PRIMARY KEY, > > ctime TIMESTAMP NOT NULL DEFAULT now(), > > mtime TIMESTAMP NOT NULL DEFAULT now(), > > code VARCHAR(20) NOT NULL CHECK (code ~ '[0-9]+'), > > label VARCHAR(200) NOT NULL CHECK (length(label) > > 0), > > UNIQUE(code) > > ); > > > > CREATE INDEX ON ldap_title (mtime); > > > > > > CREATE TABLE IF NOT EXISTS store_ldap_profile_defaults ( > > id SERIAL PRIMARY KEY, > > ref_ldap_department INTEGER NOT NULL, > > ref_ldap_title INTEGER NOT NULL, > > format VARCHAR(1) NOT NULL CHECK (format IN ('H', > 'S')), > > access_mode VARCHAR(1) NOT NULL CHECK (access_mode > IN ('R', 'W')), > > FOREIGN KEY (ref_ldap_department) REFERENCES > ldap_department (id) ON DELETE CASCADE, > > FOREIGN KEY (ref_ldap_title) REFERENCES ldap_title > (id) ON DELETE CASCADE, > > UNIQUE (ref_ldap_department, ref_ldap_title, format) > > ); > > > > CREATE INDEX ON store_ldap_profile_defaults (format); > > CREATE INDEX ON store_ldap_profile_defaults (access_mode); > > > > > > CREATE TABLE IF NOT EXISTS store_ldap_profile ( > > id SERIAL PRIMARY KEY, > > ctime TIMESTAMP NOT NULL DEFAULT now(), > > mtime TIMESTAMP NOT NULL DEFAULT now(), > > ref_store INTEGER NOT NULL, > > ref_ldap_department INTEGER NOT NULL, > > ref_ldap_title INTEGER NOT NULL, > > access_mode VARCHAR(1) NOT NULL CHECK (access_mode > IN ('R', 'W')), > > FOREIGN KEY (ref_store) REFERENCES store (id) ON > DELETE RESTRICT, > > FOREIGN KEY (ref_ldap_department) REFERENCES > ldap_department (id) ON DELETE CASCADE, > > FOREIGN KEY (ref_ldap_title) REFERENCES ldap_title > (id) ON DELETE CASCADE, > > UNIQUE (ref_store, ref_ldap_department, > ref_ldap_title) > > ); > > > > CREATE INDEX ON store_ldap_profile (mtime); > > CREATE INDEX ON store_ldap_profile (ref_store); > > > > > > DROP TRIGGER IF EXISTS touch_store_ldap_profile_trigger > > ON application.store_ldap_profile; > > > > CREATE OR REPLACE FUNCTION touch_store_ldap_profile() RETURNS TRIGGER AS $$ > > BEGIN > > UPDATE application.store SET mtime = now() WHERE id = > NEW.ref_store; > > RETURN NEW; > > END; $$ > > LANGUAGE plpgsql VOLATILE; > > > > CREATE TRIGGER touch_store_ldap_profile_trigger > > AFTER INSERT OR UPDATE ON application.store_ldap_profile > > FOR EACH ROW EXECUTE PROCEDURE touch_store_ldap_profile(); > > > > > > DROP TRIGGER IF EXISTS init_store_ldap_profiles_trigger > > ON application.store; > > > > CREATE OR REPLACE FUNCTION init_store_ldap_profiles() RETURNS TRIGGER AS $$ > > DECLARE > > r application.store_ldap_profile_defaults%rowtype; > > 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; > > > > CREATE TRIGGER init_store_ldap_profiles_trigger > > AFTER INSERT ON application.store > > FOR EACH ROW EXECUTE PROCEDURE init_store_ldap_profiles(); > > > > INSERT INTO ldap_department (code, label) VALUES > > ('03000', 'CAISSES'); > > > > INSERT INTO ldap_title (code, label) VALUES > > ('814', 'MANAGER SERV CAISSES'), > > ('837', 'RESPONSABLE SERVICE CAISSES'); > > > > INSERT INTO store_ldap_profile_defaults (ref_ldap_department, > ref_ldap_title, format, access_mode) VALUES > > ((SELECT id FROM ldap_department WHERE code = > '03000' LIMIT 1), (SELECT id FROM ldap_title WHERE code = '814' LIMIT 1), > 'H', 'R'), > > ((SELECT id FROM ldap_department WHERE code = > '03000' LIMIT 1), (SELECT id FROM ldap_title WHERE code = '837' LIMIT 1), > 'H', 'W'); > > > > > > > > --SET search_path TO "$user",public; > > >
