Обсуждение: functions - triggers cross schemas
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 triggerfrom 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 whichwould 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();
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
See:
TG_TABLE_SCHEMAAny object name not schema qualified will use search_path for resolution. Search path doesn't change upon entering a function unless the function defines its own - and your's does not.
David J.
Ok that was my oversight in understanding that the path does not change upon entering a function. I assumed that .it does by simply picking it from the schema2.test1
Thanks a bunch
Armand
On Oct 13, 2016, at 5:31 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
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_histSee:TG_TABLE_SCHEMAAny object name not schema qualified will use search_path for resolution. Search path doesn't change upon entering a function unless the function defines its own - and your's does not.David J.