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

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

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

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

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

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

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;

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

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