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 1ozdOV-0004Ea-Re for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 12:37:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozdOU-0004Mm-D7 for pgsql-general@arkaria.postgresql.org; Mon, 28 Nov 2022 12:37:54 +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 1ozdOT-0004Mb-CU for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 12:37:54 +0000 Received: from m12.mail.163.com ([220.181.12.197]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ozdON-0007AI-UD for pgsql-general@lists.postgresql.org; Mon, 28 Nov 2022 12:37:51 +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=wmneJ 0pPQBAkol2luL1Pd+jy+PTk+x6on0RLJBsQZxw=; b=nzCgnceiCOn5dPsP1XCMQ yWwHpo4b2tf8euoBCJXZXAaaTNWl7QkxgOy0mAabUtjXwAZCHnTPMvcOSa3ftvpm 9pT2dClFvi2HdAKgEKdVOtrSK/XEr5G03PZ/lmIgITIa8TF7BF0uMUAt0GeC0ijr gIEaXrdVvWkr+FF5j0vLYs= Received: from xin-PC (unknown [221.231.169.83]) by zwqz-smtp-mta-g0-4 (Coremail) with SMTP id _____wC3v2+Qq4RjG_X2AA--.36011S2; Mon, 28 Nov 2022 20:37:36 +0800 (CST) Date: Mon, 28 Nov 2022 20:37:37 +0800 From: "yin.zhb@163.com" To: pgsql-general Subject: how to implement add using upsert and trigger? X-Priority: 3 X-GUID: 41B81511-9A07-4F42-8FD4-FCBD05712304 X-Has-Attach: no X-Mailer: Foxmail 7.2.25.148[cn] Mime-Version: 1.0 Message-ID: <202211282037367351626@163.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart482686580268_=----" X-CM-TRANSID: _____wC3v2+Qq4RjG_X2AA--.36011S2 X-Coremail-Antispam: 1Uf129KBjvJXoWrZryxtF47GF1xGw1fGr4xCrg_yoW8Jry5pF Za93s2v3yYga4jyrs8Xw47AryrZrWrtr4Dtrn8Gr97ZFy3u3Z8Zws0grW0v345ury2qr1f tF4UXry8Zw4DXaDanT9S1TB71UUUUUUqnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x07U6CJdUUUUU= X-Originating-IP: [221.231.169.83] X-CM-SenderInfo: h1lqh6lke6il2tof0z/1tbiGg-HB1aEDksSuwABsq 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_NextPart482686580268_=---- Content-Type: text/plain; charset="GB2312" Content-Transfer-Encoding: base64 SGksIGFsbKO6DQogIHdoZW4gSSB1c2luZyB1cHNlcnQgYW5kIHRyaWdnZXIgdG8gdXBkYXRlIG90 aGVyIHRhYmxlIGF1dG9tYXRpY2FsbHmjug0KY3JlYXRlIHRhYmxlIHN0YXRfZGV0YWlsKGl0ZW1p ZCBiaWdpbnQgbm90IG51bGwsIHZhbHVlIGJpZ2ludCwgY2FwdGltZSBiaWdpbnQpOw0KY3JlYXRl IHRhYmxlIHN0YXRfcmVzdWx0KGl0ZW1pZCBiaWdpbnQgcHJpbWFyeSBrZXksIHZhbHVlIGJpZ2lu dCwgY250IGJpZ2ludCk7DQoNCmNyZWF0ZSBvciByZXBsYWNlIGZ1bmN0aW9uIGluZXJ0ZnVuYygp IHJldHVybnMgdHJpZ2dlciBhcyAkJA0KYmVnaW4NCiAgICBpbnNlcnQgaW50byBzdGF0X3Jlc3Vs dChpdGVtaWQsdmFsdWUsY250KSB2YWx1ZXMobmV3Lml0ZW1pZCwgbmV3LnZhbHVlLCAxKQ0KICAg IG9uIGNvbmZsaWN0KGl0ZW1pZCkgZG8gdXBkYXRlDQogICAgc2V0IHZhbHVlID0gZXhjbHVkZWQu dmFsdWUgKyBuZXcudmFsdWUsIGNudCA9IGV4Y2x1ZGVkLmNudCArMSB3aGVyZSBleGNsdWRlZC5p dGVtaWQgPSBuZXcuaXRlbWlkOw0KICAgIHJldHVybiBuZXc7DQplbmQ7DQokJA0KbGFuZ3VhZ2Ug cGxwZ3NxbDsNCg0KY3JlYXRlIHRyaWdnZXIgdHJpX2luc2VydCBhZnRlciBpbnNlcnQgb24gc3Rh dF9kZXRhaWwgZm9yIGVhY2ggcm93IGV4ZWN1dGUgZnVuY3Rpb24gaW5lcnRmdW5jKCk7DQoNCnBv c3RncmVzPSMgaW5zZXJ0IGludG8gc3RhdF9kZXRhaWwgdmFsdWVzKDEwMCwxLDEpOw0KSU5TRVJU IDAgMQ0KcG9zdGdyZXM9IyBzZWxlY3QgKiBmcm9tIHN0YXRfcmVzdWx0IDsNCiBpdGVtaWQgfCB2 YWx1ZSB8IGNudA0KLS0tLS0tLS0rLS0tLS0tLSstLS0tLQ0KICAgIDEwMCB8ICAgICAxIHwgICAx DQooMSByb3cpDQoNCnBvc3RncmVzPSMgaW5zZXJ0IGludG8gc3RhdF9kZXRhaWwgdmFsdWVzKDEw MCwxLDEpOw0KSU5TRVJUIDAgMQ0KcG9zdGdyZXM9IyBzZWxlY3QgKiBmcm9tIHN0YXRfcmVzdWx0 IDsNCiBpdGVtaWQgfCB2YWx1ZSB8IGNudA0KLS0tLS0tLS0rLS0tLS0tLSstLS0tLQ0KICAgIDEw MCB8ICAgICAyIHwgICAyDQooMSByb3cpDQoNCnBvc3RncmVzPSMgaW5zZXJ0IGludG8gc3RhdF9k ZXRhaWwgdmFsdWVzKDEwMCwxLDEpOw0KSU5TRVJUIDAgMQ0KcG9zdGdyZXM9IyBzZWxlY3QgKiBm cm9tIHN0YXRfcmVzdWx0IDsNCiBpdGVtaWQgfCB2YWx1ZSB8IGNudA0KLS0tLS0tLS0rLS0tLS0t LSstLS0tLQ0KICAgIDEwMCB8ICAgICAyIHwgICAyDQooMSByb3cpDQoNCg0KQnV0ICBJIHdhbnQg aXQgaXMgIjEwMCAzIDMiLiBTbyBob3cgSSBjYW4gZG8/DQoNCg0KDQp5aW4uemhiQDE2My5jb20N Cg== ------=_001_NextPart482686580268_=---- Content-Type: text/html; charset="GB2312" Content-Transfer-Encoding: quoted-printable =0A
Hi, all=A3=BA
  w= hen I using upsert and trigger to update other table automatically=A3=BA
create table stat_detail(itemid bigint = not null, value bigint, = captime bigint);
create tabl= e stat_result(itemid bigint = primary key, value bigint, cnt bi= gint);

create = or = replace function inertfunc()= returns trigger as $$
begin=
    insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
    on co= nflict(itemid) do update
=     set value =3D excluded.value + new.value, cnt =3D exclud= ed.cnt +1 where excluded.itemid =3D new.itemid;
   = ; return new;
end;
$$
language plpgsql;

create trigger tri_insert insert on stat_detail for each row execute function inertfunc();

postg= res=3D# insert into stat_detail values= (100,1,1);
INSERT 0 1
postgres=3D# = select * from stat_result ;
 itemid | value | cnt
100 |     1= |   1
(1 row)

postgres=3D# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=3D# select *= from stat_result ;
 = ;itemid | value | cnt
--------+-------+-----
 = ;   100 |     2 |   1 row)

postgres=3D# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=3D# select * from sta= t_result ;
 itemid | value<= /span> | cnt
--------+-------+--= ---
    100 |     2 |   2
(1 row)

= =0A

But  I want it is "100 3 3". So how I can do?<= /div>


=0A
yin.zhb@163.com
=0A ------=_001_NextPart482686580268_=------