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();