From: yin.zhb@163.com <yin.zhb@163.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: how to implement add using upsert and trigger?
Date: Mon, 28 Nov 2022 20:37:37 +0800
Message-ID: <202211282037367351626@163.com> (raw)
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.zhb@163.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: yin.zhb@163.com
Subject: Re: how to implement add using upsert and trigger?
In-Reply-To: <202211282037367351626@163.com>
References: <202211282037367351626@163.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox