Re: update in rule

On Wed, 17 Oct 2001, Nicolas KOWALSKI wrote:

> <base>=# \d adresses
> Table "adresses"
> Attribute | Type | Modifier
>
> --------------+------------------------+----------------------------------------
> id | integer | default nextval('adresses_id_seq'::text)
> <snip>
> date_maj | date | default 'NOW'
> Index: adresses_pkey
>
>
> I would like to automatically update the `date_maj' field with the
> current day date whenever an update on a row occurs.
>
> So I created a rule like :
>
> CREATE RULE adresses_date_maj
> AS ON UPDATE TO adresses
> DO UPDATE adresses SET date_maj = 'NOW' ;
>
>
> But as says the documentation, this does not work (circular problem).
>
> Does anyone known how to achieve such a behaviour ?

Use a trigger instead, something like

create function adresses_trigger() returns opaque as '
begin
NEW.date_maj := now();
return NEW;
end;' language 'plpgsql';
create trigger tr before update on adresses for each row execute
procedure adresses_trigger();