Обсуждение: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

Поиск
Список
Период
Сортировка

How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Peter Devoy
Дата:
As part of the extension I am writing I am trying to create a trigger
procedure in which the value of the primary key of the NEW or OLD row
is used. The trigger will be fired by arbitrary tables so the column
name must be dynamic.  Something like:

        pk_column := 'foo_id'; --example assignment only

        EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
        USING NEW.quote_literal(pk_column);

Out of desperation I have pretty much brute forced many weird
combinations of quote_literal, quote_ident, ::regclass, || and USING.
Unfortunately, I have not been able to get anything to work so any
help would be very much appreciated.

Thanks for reading


Peter Devoy

Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Vik Fearing
Дата:
On 01/19/2016 11:05 PM, Peter Devoy wrote:
> As part of the extension I am writing I am trying to create a trigger
> procedure in which the value of the primary key of the NEW or OLD row
> is used. The trigger will be fired by arbitrary tables so the column
> name must be dynamic.  Something like:
>
>         pk_column := 'foo_id'; --example assignment only
>
>         EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
>         USING NEW.quote_literal(pk_column);
>
> Out of desperation I have pretty much brute forced many weird
> combinations of quote_literal, quote_ident, ::regclass, || and USING.
> Unfortunately, I have not been able to get anything to work so any
> help would be very much appreciated.

Everything gets easier when you use format(). The following should do
what you want:

    EXECUTE format('INSERT INTO bar (baz) VALUES (($1).%L)', pk_column)
        USING NEW;
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Scott Mead
Дата:
On Tue, Jan 19, 2016 at 5:05 PM, Peter Devoy <peter@3xe.co.uk> wrote:
As part of the extension I am writing I am trying to create a trigger
procedure in which the value of the primary key of the NEW or OLD row
is used. The trigger will be fired by arbitrary tables so the column
name must be dynamic.  Something like:

        pk_column := 'foo_id'; --example assignment only

        EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
        USING NEW.quote_literal(pk_column);

Out of desperation I have pretty much brute forced many weird
combinations of quote_literal, quote_ident, ::regclass, || and USING.
Unfortunately, I have not been able to get anything to work so any
help would be very much appreciated.

Thanks for reading


...
    -- Dump into proper partition
    sql := 'INSERT INTO ' || v_ets_destination || ' VALUES ( ($1).*)';

    -- DEBUG
    --RAISE NOTICE 'SQL: %',sql;

    BEGIN
         EXECUTE sql USING NEW;
...
 
--
Scott Mead
Sr. Architect
OpenSCG
PostgreSQL, Java & Linux Experts

Desk   : (732) 339 3419 ext 116
Bridge: (585) 484-8032

http://openscg.com




Peter Devoy


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

Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Vik Fearing
Дата:
On 01/19/2016 11:14 PM, Vik Fearing wrote:
> On 01/19/2016 11:05 PM, Peter Devoy wrote:
>> As part of the extension I am writing I am trying to create a trigger
>> procedure in which the value of the primary key of the NEW or OLD row
>> is used. The trigger will be fired by arbitrary tables so the column
>> name must be dynamic.  Something like:
>>
>>         pk_column := 'foo_id'; --example assignment only
>>
>>         EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
>>         USING NEW.quote_literal(pk_column);
>>
>> Out of desperation I have pretty much brute forced many weird
>> combinations of quote_literal, quote_ident, ::regclass, || and USING.
>> Unfortunately, I have not been able to get anything to work so any
>> help would be very much appreciated.
>
> Everything gets easier when you use format(). The following should do
> what you want:
>
>     EXECUTE format('INSERT INTO bar (baz) VALUES (($1).%L)', pk_column)
>         USING NEW;

Argh!  I *always* type the wrong one.  It should be %I instead of %L
(identifier instead of literal).  Sorry about that.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Jim Mlodgenski
Дата:


On Tue, Jan 19, 2016 at 5:05 PM, Peter Devoy <peter@3xe.co.uk> wrote:
As part of the extension I am writing I am trying to create a trigger
procedure in which the value of the primary key of the NEW or OLD row
is used. The trigger will be fired by arbitrary tables so the column
name must be dynamic.  Something like:

        pk_column := 'foo_id'; --example assignment only

One way to define the pk_column for each table is to define it as a parameter on the CREATE TRIGGER on each table. You can then use that inside of the trigger function. 

CREATE TRIGGER foo_trigger
    BEFORE INSERT 
      ON foo
    FOR EACH ROW 
    EXECUTE PROCEDURE test_trigger('foo_id');

 

        EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
        USING NEW.quote_literal(pk_column);


NEW is really just a ROW structure so you can turn it into JSON and dynamically pull out the values however you wish.

CREATE OR REPLACE FUNCTION test_trigger()
RETURNS TRIGGER AS $$
DECLARE
  pk_column VARCHAR;
  pk_val    INT;
BEGIN
    pk_column := TG_ARGV[0];
    pk_val := row_to_json(NEW)->>pk_column;

    INSERT INTO bar (baz) VALUES (pk_val);

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

 
Out of desperation I have pretty much brute forced many weird
combinations of quote_literal, quote_ident, ::regclass, || and USING.
Unfortunately, I have not been able to get anything to work so any
help would be very much appreciated.

Thanks for reading


Peter Devoy


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

Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

От
Peter Devoy
Дата:
>Argh!  I *always* type the wrong one.  It should be %I instead of %L
You're not alone, I did the same thing in my example with quote_literal. -_-

Thank you all very much for your solutions -- I can end this 14hr day
on a high note!

Peter