agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

Trigger function - variable for schema name
5+ messages / 2 participants
[nested] [flat]

* Trigger function - variable for schema name
@ 2014-01-27 08:39  ssylla <stefansylla@gmx.de>
  0 siblings, 1 reply; 5+ messages in thread

From: ssylla @ 2014-01-27 08:39 UTC (permalink / raw)
  To: pgsql-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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Trigger function - variable for schema name
@ 2014-01-27 10:47  Glyn Astill <glynastill@yahoo.co.uk>
  parent: ssylla <stefansylla@gmx.de>
  0 siblings, 1 reply; 5+ messages in thread

From: Glyn Astill @ 2014-01-27 10:47 UTC (permalink / raw)
  To: ssylla <stefansylla@gmx.de>; pgsql-sql

> 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.



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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Trigger function - variable for schema name
@ 2014-01-27 11:00  Glyn Astill <glynastill@yahoo.co.uk>
  parent: Glyn Astill <glynastill@yahoo.co.uk>
  0 siblings, 1 reply; 5+ messages in thread

From: Glyn Astill @ 2014-01-27 11:00 UTC (permalink / raw)
  To: ssylla <stefansylla@gmx.de>; pgsql-sql





----- 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.
>


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Trigger function - variable for schema name [SOLVED]
@ 2014-01-28 06:26  ssylla <stefansylla@gmx.de>
  parent: Glyn Astill <glynastill@yahoo.co.uk>
  0 siblings, 1 reply; 5+ messages in thread

From: ssylla @ 2014-01-28 06:26 UTC (permalink / raw)
  To: pgsql-sql

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....
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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Trigger function - variable for schema name [SOLVED]
@ 2014-02-15 10:03  ssylla <stefansylla@gmx.de>
  parent: ssylla <stefansylla@gmx.de>
  0 siblings, 0 replies; 5+ messages in thread

From: ssylla @ 2014-02-15 10:03 UTC (permalink / raw)
  To: pgsql-sql

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-tp5788931p5...
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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

end of thread, other threads:[~2014-02-15 10:03 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-27 08:39 Trigger function - variable for schema name ssylla <stefansylla@gmx.de>
2014-01-27 10:47 ` Glyn Astill <glynastill@yahoo.co.uk>
2014-01-27 11:00   ` Glyn Astill <glynastill@yahoo.co.uk>
2014-01-28 06:26     ` Re: Trigger function - variable for schema name [SOLVED] ssylla <stefansylla@gmx.de>
2014-02-15 10:03       ` Re: Trigger function - variable for schema name [SOLVED] ssylla <stefansylla@gmx.de>

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