Hi, I think you need:
insert into stat_result(itemid,value,cnt) values(new.itemid,
new.value, 1)
on conflict(itemid) do update
set value = stat_result.value + new.value, cnt = stat_result.cnt +1;
excluded.value and new.value is the same value from inserted record, but
your need a current value from stat_result.
On 28.11.2022 15:37, yin....@163.com wrote:
Hi, all：
when I using upsert and trigger to update other table automatically：
create table stat_detail(itemid bigint not null, value bigint, captime
bigint);
create table stat_result(itemid bigint primary key, value bigint, cnt
bigint);
create or replace function inertfunc() returns trigger as $$
begin
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = excluded.value + new.value, cnt = excluded.cnt +1 where
excluded.itemid = new.itemid;
return new;
end;
$$
language plpgsql;
create trigger tri_insert after insert on stat_detail for each row
execute function inertfunc();
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 1 | 1
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 2 | 2
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
itemid | value | cnt
--------+-------+-----
100 | 2 | 2
(1 row)
But I want it is "100 3 3". So how I can do?
------------------------------------------------------------------------
yin....@163.com
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
The Russian Postgres Company
