pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
update in rule
8+ messages / 6 participants
[nested] [flat]

* update in rule
@ 2001-10-17 17:24 Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Nicolas KOWALSKI @ 2001-10-17 17:24 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org


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.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
@ 2001-10-17 19:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-18 17:49   ` Re: update in rule Dmitry Morozovsky <marck@rinet.ru>
  2001-11-07 10:52   ` Re: update in rule Radoslaw Stachowiak <radek@alter.pl>
  0 siblings, 2 replies; 8+ messages in thread

From: Stephan Szabo @ 2001-10-17 19:04 UTC (permalink / raw)
  To: Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>; +Cc: pgsql-admin@postgresql.org


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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-10-18 17:49   ` Dmitry Morozovsky <marck@rinet.ru>
  2001-10-18 19:14     ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-18 20:40     ` Re: [ADMIN] update in rule Oleg Lebedev <olebedev@waterford.org>
  1 sibling, 2 replies; 8+ messages in thread

From: Dmitry Morozovsky @ 2001-10-18 17:49 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>; pgsql-admin@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-18 17:49   ` Re: update in rule Dmitry Morozovsky <marck@rinet.ru>
@ 2001-10-18 19:14     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Stephan Szabo @ 2001-10-18 19:14 UTC (permalink / raw)
  To: Dmitry Morozovsky <marck@rinet.ru>; +Cc: Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>; pgsql-admin@postgresql.org

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).





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [ADMIN] update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-10-18 17:49   ` Re: update in rule Dmitry Morozovsky <marck@rinet.ru>
@ 2001-10-18 20:40     ` Oleg Lebedev <olebedev@waterford.org>
  1 sibling, 0 replies; 8+ messages in thread

From: Oleg Lebedev @ 2001-10-18 20:40 UTC (permalink / raw)
  To: Dmitry Morozovsky <marck@rinet.ru>; pgsql-sql

You can use the following to install plpgsql:

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
        HANDLER plpgsql_call_handler
        LANCOMPILER 'PL/pgSQL';

I assume that you have default installation of postgres, otherwise change the
path in the second line.

good luck

Oleg

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:
>
> 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 ***
> ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-11-07 10:52   ` Radoslaw Stachowiak <radek@alter.pl>
  2001-11-07 12:43     ` Re: update in rule Sean K. Sell <sean@nist.gov>
  2001-11-07 17:07     ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Radoslaw Stachowiak @ 2001-11-07 10:52 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

*** 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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-11-07 10:52   ` Re: update in rule Radoslaw Stachowiak <radek@alter.pl>
@ 2001-11-07 12:43     ` Sean K. Sell <sean@nist.gov>
  1 sibling, 0 replies; 8+ messages in thread

From: Sean K. Sell @ 2001-11-07 12:43 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

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)



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: update in rule
  2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
  2001-10-17 19:04 ` Re: update in rule Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-11-07 10:52   ` Re: update in rule Radoslaw Stachowiak <radek@alter.pl>
@ 2001-11-07 17:07     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Stephan Szabo @ 2001-11-07 17:07 UTC (permalink / raw)
  To: Radoslaw Stachowiak <radek@alter.pl>; +Cc: pgsql-admin@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.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-11-07 17:07 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-17 17:24 update in rule Nicolas KOWALSKI <Nicolas.Kowalski@imag.fr>
2001-10-17 19:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-18 17:49   ` Dmitry Morozovsky <marck@rinet.ru>
2001-10-18 19:14     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-18 20:40     ` Oleg Lebedev <olebedev@waterford.org>
2001-11-07 10:52   ` Radoslaw Stachowiak <radek@alter.pl>
2001-11-07 12:43     ` Sean K. Sell <sean@nist.gov>
2001-11-07 17:07     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox