Re: How to iterate fields into a NEW.* RECORD in a TRIGGER procedure ? - Mailing list pgsql-general

From Merlin Moncure
Subject Re: How to iterate fields into a NEW.* RECORD in a TRIGGER procedure ?
Date
Msg-id AANLkTinaZaaJ-V_0N5p9KM+kd9+EwKbR6Dzh+5xpJ2AR@mail.gmail.com
Whole thread Raw
In response to Re: How to iterate fields into a NEW.* RECORD in a TRIGGER procedure ?  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
On Thu, Oct 14, 2010 at 5:32 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> 2010/10/14 Bruno Baguette <bruno.baguette@gmail.com>:
>> Hello !
>>
>> I would like to write a PL/PGSQL trigger that will store changes (Table
>> name / Column name / primary key id / OLD value / NEW value) in several
>> tables.
>>
>> As I said, this trigger must be usable for every table (it assumes that
>> theses table will have only one primary key).
>>
>> In order to that, I have to iterate fields into the NEW.*
>> How can I do that iteration ?
>>
>> Many thanks in advance for any tips !
>>
>
> look on plperl, plpython or pltcl. You cannot do it simple and well in plpgsql.

this seems to work pretty well! (9.0 only though).

create table foo(a int, b int, c int);

create or replace function foo_insert() returns trigger as
$$
declare
  r record;
begin
  for r in select * from each(hstore(new))
  loop
    raise notice 'col=% value=%', r.key, r.value;
  end loop;

  return new;
end;
$$ language plpgsql;

create trigger on_foo_insert after insert on foo
  for each row execute procedure foo_insert();

merlin

pgsql-general by date:

Previous
From: "Gauthier, Dave"
Date:
Subject: Re: Adding a New Column Specifically In a Table
Next
From: Thom Brown
Date:
Subject: Re: How to remove "enter" or new line