hstore for audit logging: Finding differences between two hstore values

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/