From: | "Armand Pirvu (home)" <armand(dot)pirvu(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | functions - triggers cross schemas |
Date: | 2016-10-13 22:18:38 |
Message-ID: | 84F4F101-378F-46D5-B847-3FB475EFF1CF@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2 schemas , schema1 and schema2, with same tables , a base table and a tracking one.
Search path is set to schema1 by default.
insert into schema2.test1 (col2 , col3) values ('foo1','foo2')
I get an entry in schema1.test1_hist and not in schema2.test1_hist
I understand that the trigger inherits the schema of its table. But does that mean that
a - the trigger will be created in the same schema
or
b - it will use the current schema and pass it to the function ? It sure seems so , even if I drop the function and trigger from schema1 I still have the same behavior. Even I do
"CREATE TRIGGER test1_audit_trig_prev
after insert or update or delete on schema2.test1
FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func_prev();
"
I still get the same behavior
The only way I could make it to add in each function
SET search_path = schema1 and SET search_path = schema2
I was expecting since I am doing an insert into schema2.test1 from schema1 to see executed the function from schema2 which would then put data in schema2.test1_hist
I did play with security definer/invoker but no luck
Any other suggestion other than hardcoding in set search_path within the function code, and aside plproxy ?
Thanks
Armand
-- Code
In both schemas I have
create table test1 (col1 bigserial, col2 char(10), col3 char(10), primary key (col1, col2));
create table test1_hist (
stmt_seq bigint not null ,
stmt_type char(6) not null,
stmt_subtype char(1) not null,
stmt_date timestamp not null,
like test1);
In schema1
create sequence seq_audit_func;
CREATE OR REPLACE FUNCTION tbl_audit_func () RETURNS TRIGGER AS $$
DECLARE
temp_new RECORD;
temp_old RECORD;
tgopdet1 char(1);
tgopdet2 char(1);
vdate timestamp;
begin
IF (TG_OP = 'INSERT') THEN
temp_new := NEW;
tgopdet2='I';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func'), TG_OP, tgopdet2, vdate, temp_new;
END IF;
IF (TG_OP = 'UPDATE') THEN
temp_old := OLD;
temp_new := NEW;
tgopdet1='D';
tgopdet2='I';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func'), TG_OP, tgopdet1, vdate, temp_old;
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func'), TG_OP, tgopdet2, vdate, temp_new;
END IF;
IF (TG_OP = 'DELETE') THEN
temp_old := OLD;
tgopdet1='D';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func'), TG_OP, tgopdet1, vdate, temp_old;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql ;
drop trigger test1_audit_trig on test1;
CREATE TRIGGER test1_audit_trig
after insert or update or delete on test1
FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func();
In schema2
create sequence seq_audit_func_prev;
CREATE OR REPLACE FUNCTION tbl_audit_func_prev () RETURNS TRIGGER AS $$
DECLARE
temp_new RECORD;
temp_old RECORD;
tgopdet1 char(1);
tgopdet2 char(1);
vdate timestamp;
begin
IF (TG_OP = 'INSERT') THEN
temp_new := NEW;
tgopdet2='I';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func_prev'), TG_OP, tgopdet2, vdate, temp_new;
END IF;
IF (TG_OP = 'UPDATE') THEN
temp_old := OLD;
temp_new := NEW;
tgopdet1='D';
tgopdet2='I';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func_prev'), TG_OP, tgopdet1, vdate, temp_old;
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func_prev'), TG_OP, tgopdet2, vdate, temp_new;
END IF;
IF (TG_OP = 'DELETE') THEN
temp_old := OLD;
tgopdet1='D';
vdate = now();
EXECUTE 'INSERT INTO ' || TG_TABLE_NAME::regclass || '_hist SELECT $1,$2,$3,$4, $5.* ' using nextval('seq_audit_func_prev'), TG_OP, tgopdet1, vdate, temp_old;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql ;
drop trigger test1_audit_trig_prev on test1;
CREATE TRIGGER test1_audit_trig_prev
after insert or update or delete on test1
FOR EACH ROW EXECUTE PROCEDURE tbl_audit_func_prev();
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2016-10-13 22:26:21 | Re: SERIALIZABLE and INSERTs with multiple VALUES |
Previous Message | Jeff Janes | 2016-10-13 21:48:38 | postgres_fdw and permissions |