Thread: 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
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.
> 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';
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.
----- 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.
>
That is exactly what I was looking for! It resolved all my problems and now I
can use the schema name as a variable in my trigger function, using
'execute' and the string concatenation parameters ('||') which I was not
able to implement before (thanks for the example Glyn!).
I finally used TG_TABLE_SCHEMA instead of passing the argument with TG_ARGV.
Here the complete solution:
CREATE OR REPLACE FUNCTION trigger_function1() RETURNS trigger AS
$$ begin EXECUTE 'select table2.id from ' || quote_ident(TG_TABLE_SCHEMA) ||
'.table2' || ' where value1 ' || ' = ' || quote_literal(new.value1) into
new.id; new.columnx=function1(TG_TABLE_SCHEMA,value1); return new;
end:
$$
language plpgsql;
CREATE TRIGGER trigger_function1 BEFORE INSERT ON schema1.table1 FOR EACH ROW EXECUTE PROCEDURE trigger_function1();
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Trigger-function-variable-for-schema-name-tp5788931p5789155.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
I even found another way of doing it, since Postgres 9.1 you it is possible
to use "execute format" which seems a little easier to use than the method
with the string concatenation parameters:
CREATE OR REPLACE FUNCTION trigger_function1() RETURNS trigger AS
$$ begin execute format(' select table2.id from %I.table2 where value1=%L
', TG_TABLE_SCHEMA, new.value1) using new into new.id; return new;
end:
$$
language plpgsql;
"%I" replaces the quote_ident method and "%L" the quote_literal method.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SOLVED-Trigger-function-variable-for-schema-name-tp5788931p5792154.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.