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();
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
