pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how to implement add using upsert and trigger?
5+ messages / 3 participants
[nested] [flat]

* how to implement add using upsert and trigger?
@ 2022-11-28 12:37  yin.zhb@163.com <yin.zhb@163.com>
  0 siblings, 2 replies; 5+ messages in thread

From: yin.zhb@163.com @ 2022-11-28 12:37 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@lists.postgresql.org>

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


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how to implement add using upsert and trigger?
@ 2022-11-28 12:47  Dominique Devienne <ddevienne@gmail.com>
  parent: yin.zhb@163.com <yin.zhb@163.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Dominique Devienne @ 2022-11-28 12:47 UTC (permalink / raw)
  To: yin.zhb@163.com <yin.zhb@163.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

On Mon, Nov 28, 2022 at 1:37 PM yin.zhb@163.com <yin.zhb@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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Re: how to implement add using upsert and trigger?
@ 2022-11-28 12:57  yin.zhb@163.com <yin.zhb@163.com>
  parent: Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: yin.zhb@163.com @ 2022-11-28 12:57 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

em, which is does not matter


yin.zhb@163.com
 
From: Dominique Devienne
Date: 2022-11-28 20:47
To: yin.zhb@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.zhb@163.com <yin.zhb@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
 


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: how to implement add using upsert and trigger?
@ 2022-11-28 13:26  Pavel Luzanov <p.luzanov@postgrespro.ru>
  parent: yin.zhb@163.com <yin.zhb@163.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Pavel Luzanov @ 2022-11-28 13:26 UTC (permalink / raw)
  To: yin.zhb@163.com <yin.zhb@163.com>; pgsql-general <pgsql-general@lists.postgresql.org>

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.zhb@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.zhb@163.com

-- 
Pavel Luzanov
Postgres Professional:https://postgrespro.com
The Russian Postgres Company

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Re: how to implement add using upsert and trigger?
@ 2022-11-28 14:20  yin.zhb@163.com <yin.zhb@163.com>
  parent: Pavel Luzanov <p.luzanov@postgrespro.ru>
  0 siblings, 0 replies; 5+ messages in thread

From: yin.zhb@163.com @ 2022-11-28 14:20 UTC (permalink / raw)
  To: Pavel Luzanov <p.luzanov@postgrespro.ru>; pgsql-general <pgsql-general@lists.postgresql.org>

Thank you, this result is what I want



yin.zhb@163.com
 
From: Pavel Luzanov
Date: 2022-11-28 21:26
To: yin.zhb@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.zhb@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.zhb@163.com

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company


^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2022-11-28 14:20 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-28 12:37 how to implement add using upsert and trigger? yin.zhb@163.com <yin.zhb@163.com>
2022-11-28 12:47 ` Dominique Devienne <ddevienne@gmail.com>
2022-11-28 12:57   ` yin.zhb@163.com <yin.zhb@163.com>
2022-11-28 13:26 ` Pavel Luzanov <p.luzanov@postgrespro.ru>
2022-11-28 14:20   ` yin.zhb@163.com <yin.zhb@163.com>

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