DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=Rk1YokeqeDzv01TEoCyHJdr6/MnMHGzYt4niU9JwqfE/1X5bgbIR6MDVOmfmThbTteGV4hdWC7bxavMYq0XsomsReZLmBj7olgt1/5Wr1wZvh7kvHTdk4+IsAy4GEJESKhCA5PSS347yJ9xdVM1RzjFaLHpZexPh2GdNYMPmmYk=;
References: <1390811996770-5788931.post@n5.nabble.com>
Message-ID: <1390819626.71225.YahooMailNeo@web133206.mail.ir2.yahoo.com>
Date: Mon, 27 Jan 2014 10:47:06 +0000 (GMT)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
Subject: Re: Trigger function - variable for schema name
To: ssylla <stefansylla@gmx.de>,
 "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <1390811996770-5788931.post@n5.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

> From: ssylla <stefansylla@gmx.de>

>To: pgsql-sql@postgresql.org=20
>Sent: Monday, 27 January 2014, 8:39
>Subject: [SQL] Trigger function - variable for schema name
>=20
>
>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()
>=A0 RETURNS trigger AS
>$BODY$
>=A0 =A0 declare my_schema text;
>=A0 =A0 begin
>=A0 =A0 =A0 =A0 my_schema :=3D TG_ARGV[0];
>=A0 =A0 =A0 =A0 select table2.id into new.id from my_schema.table2;
>=A0 =A0 =A0 =A0 new.columnx=3Dfunction1(my_schema,value1);
>=A0 =A0=A0=A0return new;
>end:
>$$
>language plpgsql
>CREATE TRIGGER trigger_function1
>=A0 BEFORE INSERT
>=A0 ON schema1.table1
>=A0 FOR EACH ROW
>=A0 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()
=A0 RETURNS trigger AS
$BODY$
=A0=A0=A0 declare my_schema text;
=A0=A0=A0 begin
=A0=A0=A0=A0=A0=A0=A0 my_schema :=3D TG_ARGV[0];
=A0=A0=A0=A0=A0=A0=A0 EXECUTE 'select table2.id into new.id from ' || quote=
_ident(my_schema) || '.table2';
=A0=A0=A0=A0=A0=A0=A0 new.columnx=3Dfunction1(my_schema,value1);
=A0=A0=A0 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 sche=
ma
>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_SC=
HEMA 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.


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