Skip site navigation (1) Skip section navigation (2)

Re: create rule and last record

From: Ernest Chiarello <ernest(at)dr7(dot)cnrs(dot)fr>
To: Ernest Chiarello <ernest(at)rhone-alpes(dot)cnrs(dot)fr>
Cc: pgsql-novice(at)postgresql(dot)org, georget(at)admin(dot)in2p3(dot)fr, larbot(at)graal(dot)dr7(dot)cnrs(dot)fr
Subject: Re: create rule and last record
Date: 2001-03-29 07:56:52
Message-ID: 3AC2EAC4.5B35B504@dr7.cnrs.fr (view raw or flat)
Thread:
Lists: pgsql-novice
Ernest Chiarello wrote:

> i use a "create rule on insert" in order to obtain the
> sum on the column montant in the column cumul.
> but the last record of the table is never updated. why ?
>
>
> DROP
> CREATE
> DROP
> CREATE
> CREATE 9606604 1
> INSERT 9606605 1
>      id     | montant | cumul
> -----+------+-------
>  toto       |      10  |
> (1 row)
>
> INSERT 9606606 1
>      id     | montant | cumul
> -----+------+-------
>  toto       |      10  |    10
>  toto       |      20  |
> (2 rows)
>
>
> here are the queries :
>
> drop table test3;
>
> create table test3 (id char(10), montant float8, cumul float8);
>
> drop function somme(bpchar);
>
> create function somme(bpchar) returns float8 as
> 'select sum(montant) from test3 where
> id = \$1' language 'sql';
>
> create rule example_rule as
> on insert to test3
> do
> update test3 set cumul = somme(new.id);
>
> insert into test3 (id,montant) values ('toto','10');
>
> select * from test3;
>
> insert into test3 (id,montant) values ('toto','20');
>
> select * from test3;
>
>
>
> --
> Ernest CHIARELLO, chiarello(at)dr7(dot)cnrs(dot)fr, Tel:04-72-44-56-77, Fax:04-72-44-56-73
> CNRS - Delegation Vallee du Rhone
> 2 avenue Albert Einstein 69609 VILLEURBANNE CEDEX - FRANCE
>
>

we found  a solution. the problem is that :

"Changes made by query Q are visible by queries which are started after
query Q, no matter whether they are started inside Q (during the
execution of Q) or after Q is
       done."
(http://www.postgresql.org/docs/postgres/triggers20281.htm)

consequently "The pseudo relations NEW and OLD become useful"
(http://www.postgresql.org/docs/postgres/rules19639.htm)

and 2 tables were used instead of one.


drop table donnees;
drop table cumuls;

create table donnees (id char(10), montant float8);
create table cumuls (id char(10), cumul float8);
insert into cumuls (id,cumul) values ('titi','0');

drop function get_cumul(bpchar);

create function get_cumul(bpchar) returns float8 as
'select sum(montant) from donnees where
id = \$1' language 'sql';

drop rule cumuls_insert_rule;

create rule cumuls_insert_rule as
on insert to donnees
do
update cumuls set cumul = get_cumul(id) + new.montant;

insert into donnees (id,montant) values ('titi','15');

select * from donnees;
select * from cumuls;

insert into donnees (id,montant) values ('titi','10');

select * from donnees;
select * from cumuls;

insert into donnees (id,montant) values ('titi','100');

select * from donnees;
select * from cumuls;

drop rule cumuls_update_rule;

create rule cumuls_update_rule as
on update to donnees
do
update cumuls set cumul = get_cumul(id) - old.montant + new.montant;

update donnees set montant=100 where montant='10';

select * from donnees;
select * from cumuls;


DROP
DROP
CREATE
CREATE
INSERT 9696788 1
DROP
CREATE
ERROR:  Rule or view 'cumuls_insert_rule' not found
CREATE 9696790 1
INSERT 9696791 1
     id     | montant
------------+---------
 titi       |      15
(1 row)

     id     | cumul
------------+-------
 titi       |
(1 row)

INSERT 9696792 1
     id     | montant
------------+---------
 titi       |      15
 titi       |      10
(2 rows)

     id     | cumul
------------+-------
 titi       |    25
(1 row)

INSERT 9696793 1
     id     | montant
------------+---------
 titi       |      15
 titi       |      10
 titi       |     100
(3 rows)

     id     | cumul
------------+-------
 titi       |   125
(1 row)

ERROR:  Rule or view 'cumuls_update_rule' not found
CREATE 9696794 1
UPDATE 1
     id     | montant
------------+---------
 titi       |      15
 titi       |     100
 titi       |     100
(3 rows)

     id     | cumul
------------+-------
 titi       |   215
(1 row)



--
Ernest CHIARELLO, chiarello(at)dr7(dot)cnrs(dot)fr, Tel:04-72-44-56-77, Fax:04-72-44-56-73
CNRS - Delegation Vallee du Rhone
2 avenue Albert Einstein 69609 VILLEURBANNE CEDEX - FRANCE


In response to

pgsql-novice by date

Next:From: Vijay DevalDate: 2001-03-29 09:42:55
Subject: Re: Query performance question
Previous:From: ADBAAMDDate: 2001-03-28 22:49:09
Subject: Re: Error in the starting of Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group