Trigger function - variable for schema name

Dear list,

I have the following trigger function an try to use TG_ARGV as a variable
for the schema name of the table that caused the trigger:

CREATE OR REPLACE FUNCTION trigger_function1() RETURNS trigger AS
$BODY$   declare my_schema text;   begin       my_schema := TG_ARGV[0];       select table2.id into new.id from
my_schema.table2;      new.columnx=function1(my_schema,value1);    return new;
 
end:
$$
language plpgsql
CREATE TRIGGER trigger_function1 BEFORE INSERT ON schema1.table1 FOR EACH ROW EXECUTE PROCEDURE
trigger_function1('schema1');

Using the trigger I get the following message:
ERROR: schema "my_schema" does not exist

So far I tried another option by temporarily changing the search path, but
that might cause problems with other users who are working on other schemas
of the database at the same time. That's why I would like to write the
trigger in a way that it will only perform on the specified schema, but not
changing the global search_path of the database.
I also tried using dynamic sql with "execute format('...', TG_TABLE_SCHEMA);
but that will only work inside the trigger, not if I want to pass the schema
name to another function that is called from within the trigger.

Stefan



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Trigger-function-variable-for-schema-name-tp5788931.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.