From: | "yin(dot)zhb(at)163(dot)com" <yin(dot)zhb(at)163(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | how to implement add using upsert and trigger? |
Date: | 2022-11-28 12:37:37 |
Message-ID: | 202211282037367351626@163.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)zhb(at)163(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2022-11-28 12:47:37 | Re: how to implement add using upsert and trigger? |
Previous Message | Torsten Förtsch | 2022-11-28 10:28:47 | Re: Replicating an existing (huge) database |