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.
