how to implement add using upsert and trigger?
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
Re: Re: how to implement add using upsert and trigger?
em， which is does not matter yin@163.com From: Dominique Devienne Date: 2022-11-28 20:47 To: yin@163.com CC: pgsql-general Subject: Re: how to implement add using upsert and trigger? On Mon, Nov 28, 2022 at 1:37 PM yin@163.com wrote: > on conflict(itemid) do update > set value = excluded.value + new.value, cnt = excluded.cnt +1 where > excluded.itemid = new.itemid; OT, but isn't `where excluded.itemid = new.itemid` redundant, given `on conflict(itemid)`? I'm asking more because I'm not sure, for my own education. Thanks, --DD
Re: Re: how to implement add using upsert and trigger?
Thank you, this result is what I want yin@163.com From: Pavel Luzanov Date: 2022-11-28 21:26 To: yin@163.com; pgsql-general Subject: Re: how to implement add using upsert and trigger? 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
