Объединить данные hstore из нескольких строк

Представьте, что у меня есть таблица с этим определением:

CREATE TABLE test (
  values HSTORE NOT NULL
);

Представьте, что я вставляю несколько записей и получаю следующее:

values
-----------------------------
"a"=>"string1","b"=>"string2"
"b"=>"string2","c"=>"string3"

Могу ли я каким-либо образом сделать агрегированный запрос, который даст мне новый hstore с объединенными ключами (и значениями) для всех строк.

Псевдозапрос:

SELECT hstore_sum(values) AS value_sum FROM test;

Желаемый результат:

value_sum
--------------------------------------------
"a"=>"string1","b"=>"string2","c"=>"string3"

Я знаю о возможных конфликтах с разными значениями для каждого ключа, но в моем случае порядок/приоритет выбора значения не важен (он даже не должен быть детерминированным, так как они будут одинаковыми для одного и того же ключа) .

Возможно ли это из коробки или вам нужно использовать какие-то специальные самодельные функции SQL или другие, чтобы сделать это?


postgresql aggregate hstore
person Christian P.    schedule 15.09.2014    source источник

Ответы (3)


arrow_upward
5
arrow_downward

Вы можете делать много вещей, например:

Моей первой мыслью было использовать each(). функцию и агрегировать ключи и значения по отдельности, например:

SELECT  hstore(array_agg(key), array_agg(value))
FROM    test,
LATERAL each(hs);

Но это работает хуже всего.

Вы также можете использовать функцию hstore_to_array(), для создания массива изменения значения ключа, например (@JakubKania):

SELECT  hstore(array_agg(altering_pairs))
FROM    test,
LATERAL unnest(hstore_to_array(hs)) altering_pairs;

Но это еще не идеально.

Вы можете полагаться на представление значений hstore и создать строку, которая будет содержать все ваши пары:

SELECT  hstore(string_agg(nullif(hs::text, ''), ','))
FROM    test;

Это довольно быстро. Однако при желании вы можете использовать настраиваемую функцию агрегирования (который может использовать встроенную конкатенацию hstore):

CREATE AGGREGATE hstore_sum (hstore) (
    SFUNC = hs_concat(hstore, hstore),
    STYPE = hstore
);

-- i used the internal function (hs_concat) for the concat (||) operator,
-- if you do not want to rely on this function,
-- you could easily write an equivalent in a custom SQL function

SELECT hstore_sum(hs)
FROM   test;

SQLFiddle

person pozs    schedule 15.09.2014
comment
Как это работает с внутренней функцией? Кроме того, мне нужно создать функцию AGGREGATE для каждого подключения или только один раз? (Например, CREATE EXTENSION hstore — одноразовая вещь). - person Christian P.; 16.09.2014
comment
@КристианП. внутренний (в данном контексте) означает, что он не задокументирован. В документации hstore указан оператор конкатенации ||, но не его базовая функция (hs_concat). Обычно это должно означать, что создатели hstore хотят, чтобы их пользователи использовали оператор вместо функции (например, потому что эта функция может быть деталью реализации). -- Да, вам нужно объявить агрегатную функцию только один раз, как обычные пользовательские функции, и использовать ее каждый раз, когда захотите. - person pozs; 16.09.2014
comment
Хорошо, имеет смысл. Меня беспокоит только то, что внутренняя функция переименовывается, изменяется и т. д., тогда как я предполагаю, что они сохраняют одинаковые функции по причинам паритета между версиями. Хотя теперь он работает так, как ожидалось :) - person Christian P.; 16.09.2014
comment
@КристианП. если вы беспокоитесь, вы можете создать для этой цели пользовательскую функцию, например. CREATE FUNCTION hstore_concat(hstore, hstore) RETURNS hstore LANGUAGE SQL AS 'SELECT $1 || $2'; - person pozs; 16.09.2014
comment
Хорошая идея. Спасибо за (очень) полный ответ :) - person Christian P.; 16.09.2014

arrow_upward
0
arrow_downward

Для этого нет встроенной функции, но hstore предлагает несколько функций, которые позволяют преобразовать его во что-то другое, например, в массив. Итак, мы приводим его к массиву, объединяем массивы и создаем hstore из окончательного массива:

SELECT hstore(array_agg(x)) FROM 
(SELECT unnest(hstore_to_array(hs)) AS x
  FROM test)
as q;

http://sqlfiddle.com/#!15/cb11a/1

P.S. Некоторые другие комбинации (например, использование JSON) могут быть более эффективными.

person Jakub Kania    schedule 15.09.2014

arrow_upward
0
arrow_downward

Это то, что я написал, что сейчас работает в производстве. Я избегаю чрезмерного преобразования между типами, например. hstore и array. Я также не использую hs_concat как sfunc напрямую, так как это приведет к NULL, если какой-либо из хэшей, которые он агрегирует, будет NULL.

CREATE OR REPLACE FUNCTION public.agg_hstore_sum_sfunc(state hstore, val hstore)
RETURNS hstore AS $$
BEGIN
    IF val IS NOT NULL THEN
        IF state IS NULL THEN
            state := val;
        ELSE
            state := state || val;
        END IF;
    END IF;
    RETURN state;
END;
$$ LANGUAGE 'plpgsql';

CREATE AGGREGATE public.sum(hstore) (
    SFUNC = public.agg_hstore_sum_sfunc,
    STYPE = hstore
);
person oᴉɹǝɥɔ    schedule 02.12.2015