functions - triggers cross schemas - Mailing list 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
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();