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

Поиск
Список
Период
Сортировка
От Jim Mlodgenski
Тема Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?
Дата
Msg-id CAB_5SRfwgWNatbmarwzrSEFhXU3gVsRazt-96ijS1dXPGJ5uPA@mail.gmail.com
обсуждение исходный текст
Ответ на How can I use a dynamic column name after NEW. or OLD. in trigger procedure?  (Peter Devoy <peter@3xe.co.uk>)
Ответы Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?  (Peter Devoy <peter@3xe.co.uk>)
Список 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;

 
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Vik Fearing
Дата:
Сообщение: Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?
Следующее
От: John R Pierce
Дата:
Сообщение: Re: Building 9.4 rpm for Red Hat 5