Re: create rule and last record

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