agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: ssylla <stefansylla@gmx.de>
To: pgsql-sql@postgresql.org
Subject: Trigger function - variable for schema name
Date: Mon, 27 Jan 2014 00:39:56 -0800 (PST)
Message-ID: <1390811996770-5788931.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (5+ messages)  latest in thread

Message-ID: <1390811996770-5788931.post@n5.nabble.com>
Permalink:  ../1390811996770-5788931.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1390811996770-5788931.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: stefansylla@gmx.de
  Subject: Re: Trigger function - variable for schema name
  In-Reply-To: <1390811996770-5788931.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox