Thread: create rule and last record

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@dr7.cnrs.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

 

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@dr7.cnrs.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@dr7.cnrs.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