1

The easiest way to explain the question is in the usage of an audit trigger.

However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). What is the simplest / fastest way to do this?

Source: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L134

audit_row.row_data = hstore(NEW.*) - excluded_cols;

This obviously doesn't work but explains, hopefully, what the answer will look like.

audit_row.row_data = hstore(hstore(NEW.*) FILTER (WHERE value IS NOT NULL)) - excluded_cols;

Preference will go to answer that doesn't use an user created function, e.g. use only Postgres functions / operators.

This is for >= PostgreSQL 11.

I am looking for a solution with hstore, not json or jsonb. Alternative ways with json or jsonb may be included, but hstore is preferred.

  • Well, with jsonb you can use jsonb_strip_nulls(). What's wrong with using jsonb instead of hstore? The functions around jsonb are much more flexible and powerful anyways – a_horse_with_no_name Oct 13 at 15:30
  • @a_horse_with_no_name hstore is already in use. Switching to jsonb at this time isn't feasible. So we could convert using hstore_to_jsonb() and then jsonb_strip_nulls(). Is there a way to convert from jsonb to hstore? If that is possible, I would accept that answer. – thames Oct 13 at 23:34
  • @a_horse_with_no_name it looks like we could do something like select hstore(jsonb_each(jsonb_strip_nulls(some_hstore_data::jsonb))) from my_table; do you foresee any issues? – thames Oct 13 at 23:48
  • The only possible other way to do it without going to jsonb and back is if there is a way to filter on a set e.g. filter each(hstore), but not sure how to filter on a set. – thames Oct 13 at 23:51
  • Note: select hstore(jsonb_each(jsonb_strip_nulls(some_hstore_data::jsonb))) from my_table; doesn't work as it doesn't convert right. Maybe it could be modified to correctly convert. – thames Oct 13 at 23:59
1

To do it with built-in tools of Postgres and the hstore module exclusively, without involving jsonb, as requested, replace:

audit_row.row_data = hstore(NEW.*) - excluded_cols;

with:

audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*) - excluded_cols)
      WHERE  value IS NOT NULL
      )
   );

While unnesting the hstore vlaue anyway, you could eliminate key names in excluded_cols in the WHERE clause instead:

audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*))
      WHERE  value IS NOT NULL
      AND    key <> ANY (excluded_cols)
      )
   );

Might be a bit faster, not sure. Probably not much difference.

Also not sure how the route via jsonb and jsonb_strip_nulls() (as has been discussed in comments) performs in comparison. Can you report back with a performance comparison?

  • Your suggestion is faster than converting to jsonb and back to hstore using SELECT. I haven't tested INSERT / TRIGGER, but would assume it's comparable. SELECT hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(row_data) WHERE value IS NOT NULL ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 10000; Is Planning time: 0.172 ms Execution time: 679.332 ms and SELECT hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(row_data) WHERE value IS NOT NULL ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 10000; is Planning time: 0.111 ms Execution time: 581.399 ms – thames Oct 15 at 2:39
  • @thames: I see the same query twice. c/p hickup? – Erwin Brandstetter Oct 15 at 10:45
  • yeah c/p hickup. The query was SELECT hstore( array_agg(key), array_agg(value)) FROM jsonb_each_text(jsonb_strip_nulls(row_data::jsonb) ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 1000; with Planning time: 0.172 ms Execution time: 679.332 ms – thames Oct 16 at 0:33
  • Thanks for the feedback! – Erwin Brandstetter Oct 16 at 0:43
0

Can you try this:

  • First, declare a new variable type record, example: empty_row
  • Then, do as bellow:
EXECUTE format('select * from populate_record(null::%s.%s, '''')', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO empty_row;

audit_row.row_data = hstore(NEW.*) - empty_row - excluded_cols;

You can then reuse empty_row in different places in your function.

  • Results in an error. Doesn't seem to like the param as a type. Any suggestions? [42601] ERROR: syntax error at or near "$1" Where: PL/pgSQL function if_modified_func() line 92 at EXECUTE – thames Oct 15 at 2:08
  • I update the SQL to insert table name. Could you check it – binhgreat Oct 15 at 5:08
  • [42704] ERROR: type "logged_actions" does not exist. You can also check it. The entire audit.logged_actions code/functions trigger is github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql after you run that script then call SELECT audit.audit_table('myschema.my_table'); on the table you want to audit. – thames Oct 16 at 0:48
  • Sorry, I forgot that you use difference schema. I update to use TG_TABLE_SCHEMA to reflect it – binhgreat Oct 16 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.