Search Postgresql Archives

comparing two JSON objects in 9.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Ahoi
 
I'm developing an auditing mechanism where table contents are logged as JSON (github.com/fxku/audit).
At first I just logged the whole row everytime my triggers were fired.
Now I'm trying to log only the old state of values that have changed.
 
Therefore I compare the OLD and NEW entry by using json_each(json).
 
SELECT old.key, old.value
FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text <> old.value::text OR new.key IS NULL;
 
Now I aggregate the result to arrays of JSON and call this function, that I've written:
CREATE OR REPLACE FUNCTION audit.build_json(json_keys ANYARRAY, json_values ANYARRAY) RETURNS JSON AS
$$
DECLARE
  json_string TEXT := '{';
  delimeter TEXT := '';
  json_result JSON;
BEGIN
  FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP
    json_string := json_string || delimeter || json_keys[i] || ':' || json_values[i];
    delimeter := ',';
  END LOOP;
  json_string := json_string || '}';
  EXECUTE format('SELECT %L::json', json_string) INTO json_result;
  RETURN json_result;
END
$$
LANGUAGE plpgsql;
 
In the end the call looks like this:
SELECT audit.build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text <> old.value::text OR new.key IS NULL;
 
Everything works as expected, but it feels kinda ugly to me.
Any PG-JSON experts around to tell me a better solution?
Did i trapped into some "YouShouldNot"s?
 
Regards!
Felix Kunde

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux