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(dot)zhb(at)163(dot)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(dot)zhb(at)163(dot)com

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