|
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/ |
