Hi all
I'm using Pg 9.1, working on enhancing my audit logging so it can record not only the old and new values of a record when it changes, but what exactly changed.
It's easy to produce hstore values for the old and new records, and I've been happily logging those. I was about to start testing a switch to 'json' instead (for possible storage space savings and better composite type support in exchange for poorer searchability) - when I found myself wondering how I could log only the fields that changed using hstore.
A quick look at the hstore documentation (
http://www.postgresql.org/docs/9.1/static/hstore.html) doesn't reveal any obvious key/value pairwise set operations like union/intersection/difference. Am I missing something obvious?
Is there some clever way to get a hstore with only the _differing_ keys of two input hstores? Or will I need to break them down into arrays and manually find the differences?
For example, given:
regres=# select hstore('"x"=>"30", "y"=>"c"'), hstore('"x"=>"30", "y"=>"fred"');
hstore | hstore
---------------------+------------------------
"x"=>"30", "y"=>"c" | "x"=>"30", "y"=>"fred"
(1 row)
is there any straightforward operation between the two above hstores I can use to produce a result like:
hstore('"y"=>"fred"');
, omitting the identical "x"=>"30" ?
I suspect I'd land up having to use each(...) to do a SQL set-oriented difference using UNION/INTERSECT/EXCEPT, which will be "fun" in performance terms. Please tell me there's a clever way I've missed.
I can do this:
regress=# select a.key, a.value from each(hstore('"x"=>"30", "y"=>"c"')) AS a EXCEPT SELECT b.* FROM each(hstore('"x"=>"30", "y"=>"fred"')) b;
key | value
-----+-------
y | c
and aggregate it back into a hstore like this:
regress=# SELECT hstore( array_agg(diff.key), array_agg(diff.value) ) FROM (SELECT a.key, a.value from each(hstore('"x"=>"30", "y"=>"c"')) AS a EXCEPT SELECT b.* FROM each(hstore('"x"=>"30", "y"=>"fred"')) b) AS diff;
hstore
----------
"y"=>"c"
(1 row)
... but it feels like there must be a smarter way.
PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.0 20120507 (Red Hat 4.7.0-5), 64-bit
Fedora 17
--
Craig Ringer
POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/