Обсуждение: How to iterate fields into a NEW.* RECORD in a TRIGGER procedure ?

Поиск
Список
Период
Сортировка

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

От
Bruno Baguette
Дата:
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 !

Regards,

--
Bruno Baguette

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

От
Pavel Stehule
Дата:
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


> Regards,
>
> --
> Bruno Baguette
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

От
Merlin Moncure
Дата:
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