Thread: 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?

From

Peter Devoy

Date:

19 January 2016, 22:05:48

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?

From

Vik Fearing

Date:

19 January 2016, 22:14:29

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?

From

Scott Mead

Date:

19 January 2016, 22:16:46

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?

From

Vik Fearing

Date:

19 January 2016, 22:23:37

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?

From

Jim Mlodgenski

Date:

19 January 2016, 22:29:14

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?

From

Peter Devoy

Date:

19 January 2016, 22:34:45

>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