CODEWITHSUNDEEP
postgresqlhstore
SunWuKung
SunWuKung

Reputation: 557

Returning a set of hstore values as a set of records of a table type

I started to experiment with the audit logging function of Postgres as described in http://wiki.postgresql.org/wiki/Audit_trigger_91plus This function stores the new values of the changed record in an hstore field.

I would like to retrieve the hstore field values as a set of records of the logged table.

Postgres documentation says converting an hstore to a record type goes like this:

CREATE TABLE test (col1 integer, col2 text, col3 text);
SELECT * FROM populate_record(null::test, '"col1"=>"456", "col2"=>"zzz"');

This works for a signle record, but I don't know how to do the same with multiple records. I tried this one:

SELECT
  populate_record(null::public.usr, logged_actions.row_data)
FROM
  audit.logged_actions
WHERE
  audit.logged_actions.table_name = 'usr'

... but it returns a single composit column for each logged record of the usr table.

Any ideas how to return the set of hstore values as a set of records of type usr here?

Upvotes: 1

Views: 1317

Answers (1)

SunWuKung
SunWuKung

Reputation: 557

I found the answer here, many thanks to Sergey Konoplev:

http://www.postgresql.org/message-id/CAHot5JHzZ1LRHk3vizuTX2-Bw-C54cz-tFL7Cg=99t6LLaCoZA@mail.gmail.com

SELECT
  (populate_record(null::public.usr, logged_actions.row_data)).*
FROM
  audit.logged_actions
WHERE
  audit.logged_actions.table_name = 'usr'

Upvotes: 3

Related Questions