Search Postgresql Archives

how to implement add using upsert and trigger?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux