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

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.

Regards

Pavel Stehule

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