用其他hstore值更新hstore值

Question

我有一个汇总表，定期更新新的数据。其中一列为hstore类型。当我用新的数据更新时，如果键存在，我想要将键的值添加到键的现有值，否则我想要将这对键添加到hstore中。

现有数据：

id     sum     keyvalue
--------------------------------------
1      2       "key1"=>"1","key2"=>"1"

新数据：

id    sum      keyvalue
--------------------------------------------------
1     3        "key1"=>"1","key2"=>"1","key3"=>"1"

通缉结果：

id    sum      keyvalue
--------------------------------------------------
1     5        "key1"=>"2","key2"=>"2","key3"=>"1"

我想在插入的冲突部分中这样做。和的部分很容易。但是我还没有找到用这种方式连接商店的方法。

Answer 1

没有建造任何东西。您必须编写一个接受的函数来存储值，并以您想要的方式合并它们。

create function merge_and_increment(p_one hstore, p_two hstore)
  returns hstore
as
$$
  select hstore_agg(hstore(k,v))
  from (
    select k, sum(v::int)::text as v
    from (
      select *
      from each(p_one) as t1(k,v)
      union all
      select *
      from each(p_two) as t2(k,v)
    ) x
    group by k
  ) s
$$
language sql;

hstore_agg()函数也不是内置的，但是很容易定义它：

create aggregate hstore_agg(hstore) 
(
  sfunc = hs_concat(hstore, hstore),
  stype = hstore
);

其结果是：

select merge_and_increment(hstore('"key1"=>"1","key2"=>"1"'), hstore('"key1"=>"1","key2"=>"1","key3"=>"1"'))

是：

merge_and_increment                  
-------------------------------------
"key1"=>"2", "key2"=>"2", "key3"=>"1"

请注意，如果存在不能转换为整数的值，则该函数将不幸失败。

使用insert语句，您可以这样使用它：

insert into the_table (id, sum, data)
values (....) 
on conflict (id) do update
   set sum = the_table.sum + excluded.sum, 
       data = merge_and_increment(the_table.data, excluded.data);

Answer 2

demo:db<>fiddle

CREATE OR REPLACE FUNCTION sum_hstore(_old hstore, _new hstore) RETURNS hstore
AS $$
DECLARE
    _out hstore;
BEGIN
    SELECT
        hstore(array_agg(key), array_agg(value::text))
    FROM (
        SELECT
            key,
            SUM(value::int) AS value
        FROM (
            SELECT * FROM each('"key1"=>"1","key2"=>"1"'::hstore)

            UNION ALL

            SELECT * FROM each('"key1"=>"1","key2"=>"1","key3"=>"1"')
        ) s
        GROUP BY key
    ) s
    INTO _out;

    RETURN _out;
END;
$$
LANGUAGE plpgsql;

1. each()将键/值对展开为每对一行，其中列为键和值
2. 将text类型转换为int类型并对value进行分组/求和
3. 使用hstore函数聚合到一个新的hstore(array, array)值中。数组元素是key列的值和value列的值。

您可以执行这样的更新：

UPDATE mytable
SET keyvalue = sum_hstore(keyvalue, '"key1"=>"1","key2"=>"1","key3"=>"1"')
WHERE id = 1;