functions - triggers cross schemas

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();

Responses

Browse pgsql-general by date

  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