Creates an aggregate called hstore_merge that merges PostgreSQL's HSTORE values using the concat (||) operator

gistfile1.sql

CREATE OR REPLACE FUNCTION hstore_merge(left HSTORE, right HSTORE) RETURNS HSTORE AS $$
    SELECT $1 || $2;
$$ LANGUAGE SQL;

CREATE AGGREGATE hstore_merge (HSTORE) (
    SFUNC = hstore_merge,
    STYPE = HSTORE,
    INITCOND = ''
);

-- Example usage:
-- Assume you have a table:
--
--   CREATE TABLE audit_record (
--     object_id UUID,
--     modified_values HSTORE,
--     timestamp TIMESTAMP
--   )
--
-- where-in you store every modification to a record as an HSTORE
-- containing the fields modified at a specific time, but not
-- the fields that weren't modified.  You could use hstore_merge
-- to generate a snapshot of the object at any point in time:
--
-- SELECT
--     hstore_merge(modified_values) OVER (PARTITION BY object_id ORDER BY timestamp) AS values
-- FROM
--     audit_record
-- WHERE
--     object_id = :object_id AND
--     timestamp <= :timestamp
-- ORDER BY timestamp DESC
-- LIMIT 1

Thank you for this!

I really appreciate this, fellow developer!

This is great, but be aware, if ANY of the hstores is NULL, the result will also be NULL. Change the hstore_merge function to this:

 CREATE OR REPLACE FUNCTION hstore_merge(left HSTORE, right HSTORE) RETURNS HSTORE AS $$
  SELECT coalesce($1, '') || coalesce($2, '');
  $$ LANGUAGE SQL;

to fix that.