Date: Mon, 27 Jan 2014 00:39:56 -0800 (PST)
From: ssylla <stefansylla@gmx.de>
To: pgsql-sql@postgresql.org
Message-ID: <1390811996770-5788931.post@n5.nabble.com>
Subject: Trigger function - variable for schema name
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql