Re: Trigger function - variable for schema name - Mailing list pgsql-sql
From | Glyn Astill |
---|---|
Subject | Re: Trigger function - variable for schema name |
Date | |
Msg-id | 1390820451.59644.YahooMailNeo@web133202.mail.ir2.yahoo.com Whole thread Raw |
In response to | Re: Trigger function - variable for schema name (Glyn Astill <glynastill@yahoo.co.uk>) |
Responses |
Re: Trigger function - variable for schema name [SOLVED]
|
List | pgsql-sql |
----- Original Message ----- > From: Glyn Astill <glynastill@yahoo.co.uk> > To: ssylla <stefansylla@gmx.de>; "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org> > Cc: > Sent: Monday, 27 January 2014, 10:47 > Subject: Re: [SQL] Trigger function - variable for schema name > >> From: ssylla <stefansylla@gmx.de> > >> To: pgsql-sql@postgresql.org >> Sent: Monday, 27 January 2014, 8:39 >> Subject: [SQL] 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 >> > > To do what you're trying to do there you'd probably be best to use > EXECUTE: > > CREATE OR REPLACE FUNCTION trigger_function1() > RETURNS trigger AS > $BODY$ > declare my_schema text; > begin > my_schema := TG_ARGV[0]; > EXECUTE 'select table2.id into new.id from ' || > quote_ident(my_schema) || '.table2'; Oops, missed the select into there, and you only want that query to return one record: EXECUTE 'select table2.id from ' || quote_ident(my_schema) || '.table2' into new.id; > > new.columnx=function1(my_schema,value1); > return new; > end: > $$ > language plpgsql > > >> 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. > > > We'll I don't see why you couldn't pull the current schema with > TG_TABLE_SCHEMA and pass it as a variable to your other function, but I'm > not entirely sure what you're trying to do to be honest. >