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

On Tue, Jan 19, 2016 at 5:05 PM, Peter Devoy <peter(at)3xe(dot)co(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>