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=XyOierfwFeO29Prg5GcaJnRGFD2Q1XF65VGemKsnIQ14Gtq8U94M8vMSR4ohTQnU1WOJy1BiEFZhdQ9dYXQ+mevL0aYv26gFRKdv2UZIbBwgoJZQwZ6YbOK8yBrNI0ovh36fZQ6xRopGO3vrTSzJgW4u+DZmf7ccYZEQPiWbXXU=;
References: <1390811996770-5788931.post@n5.nabble.com>
 <1390819626.71225.YahooMailNeo@web133206.mail.ir2.yahoo.com>
Message-ID: <1390820451.59644.YahooMailNeo@web133202.mail.ir2.yahoo.com>
Date: Mon, 27 Jan 2014 11:00:51 +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: <1390819626.71225.YahooMailNeo@web133206.mail.ir2.yahoo.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


----- Original Message -----
> From: Glyn Astill <glynastill@yahoo.co.uk>
> To: ssylla <stefansylla@gmx.de>; "pgsql-sql@postgresql.org" <pgsql-sql@po=
stgresql.org>
> Cc:=20
> Sent: Monday, 27 January 2014, 10:47
> Subject: Re: [SQL] Trigger function - variable for schema name
>=20
>>  From: ssylla <stefansylla@gmx.de>
>=20
>> To: pgsql-sql@postgresql.org=20
>> Sent: Monday, 27 January 2014, 8:39
>> Subject: [SQL] Trigger function - variable for schema name
>>=20
>>=20
>> Dear list,
>>=20
>> 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:
>>=20
>> 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');
>>=20
>> Using the trigger I get the following message:
>> ERROR: schema "my_schema" does not exist
>>=20
>=20
> To do what you're trying to do there you'd probably be best to use=20
> EXECUTE:
>=20
> 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 ' ||=20
> 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' int=
o new.id;

>=20
> =A0=A0=A0=A0=A0=A0=A0 new.columnx=3Dfunction1(my_schema,value1);
> =A0=A0=A0 return new;
> end:
> $$
> language plpgsql
>=20
>=20
>> So far I tried another option by temporarily changing the search path, b=
ut
>> that might cause problems with other users who are working on other sche=
mas
>> 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('...',=20
> TG_TABLE_SCHEMA);
>> but that will only work inside the trigger, not if I want to pass the sc=
hema
>> name to another function that is called from within the trigger.
>=20
>=20
> We'll I don't see why you couldn't pull the current schema with=20
> TG_TABLE_SCHEMA and pass it as a variable to your other function, but I'm=
=20
> 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