Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ozf02-0000HP-2X for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 14:20:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozf00-0003n1-QK for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 14:20:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ozf00-0003ms-7R for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 14:20:44 +0000 Received: from m12-15.163.com ([220.181.12.15]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozezv-0008Cz-Uw for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 14:20:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=Date:From:Subject:Mime-Version:Message-ID; bh=mLpq/ pd+4CUsFJWTcR3MGWD2PNBexsLdvzV3p6lxY6M=; b=dP8YbsJiGaFnBm0U1SCvF e40kWqWPTsRbLdpYJvZhRGrcXuFSdo3arFOq1sN41MFkijJQJshWKnLmoyp9Q23E bUi5DNDC6LPySxkY6pWGgjl3cuf8FyWMe4aTHycZ/aqoW1ay7tclwKgBuVz+/r0w bERvqUH3wusTxI/p50lNpI= Received: from xin-PC (unknown [221.231.169.83]) by smtp11 (Coremail) with SMTP id D8CowABXO3uyw4RjwtZSBg--.12270S2; Mon, 28 Nov 2022 22:20:34 +0800 (CST) Date: Mon, 28 Nov 2022 22:20:35 +0800 From: "yin.zhb@163.com" To: "Pavel Luzanov" , pgsql-general Subject: Re: Re: how to implement add using upsert and trigger? References: <202211282037367351626@163.com>, <02375b28-3e55-f873-5a93-de7b965206f5@postgrespro.ru> X-Priority: 3 X-GUID: 4490805C-88DD-40E4-8E93-0F9BFC59F482 X-Has-Attach: no X-Mailer: Foxmail 7.2.25.148[cn] Mime-Version: 1.0 Message-ID: <202211282220346872210@163.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart267641826350_=----" X-CM-TRANSID: D8CowABXO3uyw4RjwtZSBg--.12270S2 X-Coremail-Antispam: 1Uf129KBjvJXoW7tw43uFy8JrWUCF1UXFy5Arb_yoW8CFWDpF Za93srZrWj93Wjyrs8Zw4xAryrXrWrtr4Dtr1DGr97ZFy3u3WUZrsIqFW0v345ury2qr15 tF4jq348Zw4DXaDanT9S1TB71UUUUUUqnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x0zRJCzZUUUUU= X-Originating-IP: [221.231.169.83] X-CM-SenderInfo: h1lqh6lke6il2tof0z/xtbB2APHB2BHMsjnCQAAso List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. ------=_001_NextPart267641826350_=---- Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: base64 VGhhbmsgeW91LCB0aGlzIHJlc3VsdCBpcyB3aGF0IEkgd2FudA0KDQoNCg0KeWluLnpoYkAxNjMu Y29tDQogDQpGcm9tOiBQYXZlbCBMdXphbm92DQpEYXRlOiAyMDIyLTExLTI4IDIxOjI2DQpUbzog eWluLnpoYkAxNjMuY29tOyBwZ3NxbC1nZW5lcmFsDQpTdWJqZWN0OiBSZTogaG93IHRvIGltcGxl bWVudCBhZGQgdXNpbmcgdXBzZXJ0IGFuZCB0cmlnZ2VyPw0KSGksIEkgdGhpbmsgeW91IG5lZWQ6 DQoNCiAgICBpbnNlcnQgaW50byBzdGF0X3Jlc3VsdChpdGVtaWQsdmFsdWUsY250KSB2YWx1ZXMo bmV3Lml0ZW1pZCwgbmV3LnZhbHVlLCAxKQ0KICAgIG9uIGNvbmZsaWN0KGl0ZW1pZCkgZG8gdXBk YXRlDQogICAgc2V0IHZhbHVlID0gc3RhdF9yZXN1bHQudmFsdWUgKyBuZXcudmFsdWUsIGNudCA9 IHN0YXRfcmVzdWx0LmNudCArMTsNCg0KZXhjbHVkZWQudmFsdWUgYW5kIG5ldy52YWx1ZSBpcyB0 aGUgc2FtZSB2YWx1ZSBmcm9tIGluc2VydGVkIHJlY29yZCwgYnV0IHlvdXIgbmVlZCBhIGN1cnJl bnQgdmFsdWUgZnJvbSBzdGF0X3Jlc3VsdC4NCg0KT24gMjguMTEuMjAyMiAxNTozNywgeWluLnpo YkAxNjMuY29tIHdyb3RlOg0KSGksIGFsbO+8mg0KICB3aGVuIEkgdXNpbmcgdXBzZXJ0IGFuZCB0 cmlnZ2VyIHRvIHVwZGF0ZSBvdGhlciB0YWJsZSBhdXRvbWF0aWNhbGx577yaDQpjcmVhdGUgdGFi bGUgc3RhdF9kZXRhaWwoaXRlbWlkIGJpZ2ludCBub3QgbnVsbCwgdmFsdWUgYmlnaW50LCBjYXB0 aW1lIGJpZ2ludCk7DQpjcmVhdGUgdGFibGUgc3RhdF9yZXN1bHQoaXRlbWlkIGJpZ2ludCBwcmlt YXJ5IGtleSwgdmFsdWUgYmlnaW50LCBjbnQgYmlnaW50KTsNCmNyZWF0ZSBvciByZXBsYWNlIGZ1 bmN0aW9uIGluZXJ0ZnVuYygpIHJldHVybnMgdHJpZ2dlciBhcyAkJA0KYmVnaW4NCiAgICBpbnNl cnQgaW50byBzdGF0X3Jlc3VsdChpdGVtaWQsdmFsdWUsY250KSB2YWx1ZXMobmV3Lml0ZW1pZCwg bmV3LnZhbHVlLCAxKQ0KICAgIG9uIGNvbmZsaWN0KGl0ZW1pZCkgZG8gdXBkYXRlDQogICAgc2V0 IHZhbHVlID0gZXhjbHVkZWQudmFsdWUgKyBuZXcudmFsdWUsIGNudCA9IGV4Y2x1ZGVkLmNudCAr MSB3aGVyZSBleGNsdWRlZC5pdGVtaWQgPSBuZXcuaXRlbWlkOw0KICAgIHJldHVybiBuZXc7DQpl bmQ7DQokJA0KbGFuZ3VhZ2UgcGxwZ3NxbDsNCmNyZWF0ZSB0cmlnZ2VyIHRyaV9pbnNlcnQgYWZ0 ZXIgaW5zZXJ0IG9uIHN0YXRfZGV0YWlsIGZvciBlYWNoIHJvdyBleGVjdXRlIGZ1bmN0aW9uIGlu ZXJ0ZnVuYygpOw0KcG9zdGdyZXM9IyBpbnNlcnQgaW50byBzdGF0X2RldGFpbCB2YWx1ZXMoMTAw LDEsMSk7DQpJTlNFUlQgMCAxDQpwb3N0Z3Jlcz0jIHNlbGVjdCAqIGZyb20gc3RhdF9yZXN1bHQg Ow0KIGl0ZW1pZCB8IHZhbHVlIHwgY250DQotLS0tLS0tLSstLS0tLS0tKy0tLS0tDQogICAgMTAw IHwgICAgIDEgfCAgIDENCigxIHJvdykNCnBvc3RncmVzPSMgaW5zZXJ0IGludG8gc3RhdF9kZXRh aWwgdmFsdWVzKDEwMCwxLDEpOw0KSU5TRVJUIDAgMQ0KcG9zdGdyZXM9IyBzZWxlY3QgKiBmcm9t IHN0YXRfcmVzdWx0IDsNCiBpdGVtaWQgfCB2YWx1ZSB8IGNudA0KLS0tLS0tLS0rLS0tLS0tLSst LS0tLQ0KICAgIDEwMCB8ICAgICAyIHwgICAyDQooMSByb3cpDQpwb3N0Z3Jlcz0jIGluc2VydCBp bnRvIHN0YXRfZGV0YWlsIHZhbHVlcygxMDAsMSwxKTsNCklOU0VSVCAwIDENCnBvc3RncmVzPSMg c2VsZWN0ICogZnJvbSBzdGF0X3Jlc3VsdCA7DQogaXRlbWlkIHwgdmFsdWUgfCBjbnQNCi0tLS0t LS0tKy0tLS0tLS0rLS0tLS0NCiAgICAxMDAgfCAgICAgMiB8ICAgMg0KKDEgcm93KQ0KDQpCdXQg IEkgd2FudCBpdCBpcyAiMTAwIDMgMyIuIFNvIGhvdyBJIGNhbiBkbz8NCg0KDQoNCnlpbi56aGJA MTYzLmNvbQ0KDQotLSAKUGF2ZWwgTHV6YW5vdgpQb3N0Z3JlcyBQcm9mZXNzaW9uYWw6IGh0dHBz Oi8vcG9zdGdyZXNwcm8uY29tClRoZSBSdXNzaWFuIFBvc3RncmVzIENvbXBhbnkNCg== ------=_001_NextPart267641826350_=---- Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0A =0A = =0A
Thank you, this result is what I want
=0A
<= br>

=0A
yin.zhb@163.com
= =0A
 
Date: 2022-11-28 21:26
Subject: Re: how to implement add using upsert and trigger?
=0A =0A = =0A =0A =0A Hi, I think you need:
=0A
=0A   &= nbsp; insert into stat_result(itemid,value,cnt) values(new.itemid,=0A n= ew.value, 1)
=0A     on conflict(itemid) do update=0A     set value =3D stat_result.value + new.value, cn= t =3D stat_result.cnt=0A +1;
=0A
=0A excluded.value and ne= w.value is the same value from inserted record,=0A but your need a curr= ent value from stat_result.
=0A
=0A
On 28.11.2022 15:37, yin.zhb@163.com=0A wrote:
=0A
= =0A
=0A =0A =0A
= Hi, all=EF=BC=9A
=0A
  when I using upsert and trigger= to update other table=0A automatically=EF=BC=9A
=0A
create table stat_detail(itemid bigint not null, value bigint, captime= bigint);
create table stat_result(itemid bigint primary ke= y, value bigint, cnt bigint);
=0A
create or replace function inertfunc() returns trigger as $$
begin
    insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
    on conflict(it= emid) do update
  &n= bsp; set value =3D excluded.value + new.value, cnt =3D excluded.cnt +<= span style=3D"color: #b5cea8;">1 wh= ere excluded.itemid =3D new.itemid;
    return new;
end;
$$
language plpgsql;
=0A
create trigger tri_insert after insert on stat_detail for each ro= w execute function inertfunc();
=0A
postgres=3D# insert i= nto stat_detail values(100,1,1);
INSERT 0 1
postgres=3D# select * from<= /span> stat_result ;
 itemid | value | cnt
--------+= -------+-----
    100 |     1 | &= nbsp; 1
(1 row)
= =0A
postgres=3D# insert into stat_detail values(100,1,1);<= /div>
INSERT 0 1
= postgres=3D# select * from stat_result ;
 itemid | value | cnt
--------+-------+-----
    100 |     2 |   2
(1 row)
=0A
postgres=3D# insert into stat_detail= values(100,1,1);
INSERT= 0 1
postgres=3D# sel= ect * from stat_result ;
 itemid | value | cnt
--------+-------+-----
    100 |   &= nbsp; 2 |   2
(1= row)
=0A
=0A
<= br>=0A
=0A
But  I want it is "100 3 3". So how I= can do?
=0A

=0A
=0A
=0A =
=0A
=0A = =0A
=0A
=0A
=0A
= =0A
-- =0APavel Luzanov=0APost=
gres Professional: https://postgrespro.com=0AThe Russian Postgres Company=0A  =0A
=0A ------=_001_NextPart267641826350_=------