Обсуждение: update in rule

Hello.

We are running PostgreSQL 7.1.3 on Debian GNU/Linux.
We maintain for our intranet something like an addressbook :

<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 ?

Thanks in advance,
Nicolas.

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

On Wed, 17 Oct 2001, Stephan Szabo wrote:

SS> Use a trigger instead, something like
SS>
SS> create function adresses_trigger() returns opaque as '
SS> begin
SS>  NEW.date_maj := now();
SS>  return NEW;
SS> end;' language 'plpgsql';

hmm. it seems defaul pgsql installation does not contains definition for
plpgsql language:

test=# select * from pg_language ;
 lanname  | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
 internal | f       | f            |             0 | n/a
 C        | f       | f            |             0 | /bin/cc
 sql      | f       | f            |             0 | postgres
(3 rows)

however, plpgsql.so is in pgsql lib directory (though it is *NOT* visible
by ldconfig -r)

My installation is 7.1.3 under FreeBSD 4-stable. Is it somehow my fault or
does it need to manually do something like

create function plpgsql_handler() ...
create language 'plpgsql' ...

? Please give me advise how exactly should these statements look?

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

On Thu, 18 Oct 2001, Dmitry Morozovsky wrote:

> On Wed, 17 Oct 2001, Stephan Szabo wrote:
>
> SS> Use a trigger instead, something like
> SS>
> SS> create function adresses_trigger() returns opaque as '
> SS> begin
> SS>  NEW.date_maj := now();
> SS>  return NEW;
> SS> end;' language 'plpgsql';
>
> hmm. it seems defaul pgsql installation does not contains definition for
> plpgsql language:

Yeah, it's not added by default.

> My installation is 7.1.3 under FreeBSD 4-stable. Is it somehow my fault or
> does it need to manually do something like

The easiest way is to use the createlang script from the shell.
createlang plpgsql <database>

I think if you add the hander to template1 any new databases
will get it automatically (you'll have to add it to any existing
dbs though).

*** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Wednesday, 17.October.2001, 12:04 -0700]:
> 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();

why sould I use before (instead of after)?
isnt it a little dangerous in transactions? /hope not/

I know difference between both forms. The question is rather about if
timestamp updating shouldnt be run AFTER update which I think is closer
to a real world situations (of course evth vary). But maybe there are
some probles with after, which I dont know about?

Just being curious...

--
radoslaw.stachowiak.........................................http://alter.pl/

If you do the try to set the timestamp AFTER the Update the record is already written and your change would
not be refected. THAT is the difference between BEFORE and AFTER as I understand it.


Radoslaw Stachowiak wrote:

> *** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Wednesday, 17.October.2001, 12:04 -0700]:
> > 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();
>
> why sould I use before (instead of after)?
> isnt it a little dangerous in transactions? /hope not/
>
> I know difference between both forms. The question is rather about if
> timestamp updating shouldnt be run AFTER update which I think is closer
> to a real world situations (of course evth vary). But maybe there are
> some probles with after, which I dont know about?
>
> Just being curious...
>
> --
> radoslaw.stachowiak.........................................http://alter.pl/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Wed, 7 Nov 2001, Radoslaw Stachowiak wrote:

> *** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Wednesday, 17.October.2001, 12:04 -0700]:
> > 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();
>
> why sould I use before (instead of after)?
> isnt it a little dangerous in transactions? /hope not/
>
> I know difference between both forms. The question is rather about if
> timestamp updating shouldnt be run AFTER update which I think is closer
> to a real world situations (of course evth vary). But maybe there are
> some probles with after, which I dont know about?

It's pretty much 6 of one, half dozen of another AFAICS.  This doesn't
actually update the table per-se, it updates the copy of the row that
you've asked to update the row to.  The actual update changes the date
column.