mandrews · March 18, 2014 18:32 · Jun 28, 2012
diff --git a/gistfile1.sql b/gistfile1.sql
@@ -0,0 +1,33 @@
+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
No results found