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-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Christopher BROWN 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; |
