pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate 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