Re: How to iterate fields into a NEW.* RECORD in a TRIGGER procedure ?

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