0

I'm using (the quite nice) tutorial from

http://cjauvin.blogspot.fi/2013/05/impossibly-lean-audit-system-for.html

for making a simple audit trigger in my PostgreSQL 9.6 DB. I'm however trying to make a separate "audit"-schema for it so I created it and modified the table names, installed the hstore extension etc.

However, when I cause a trigger to fire on my public.product table, I get a

ERROR: function hstore(product) does not exist.

I tried to create the hstore extension in the public schema also in case the execution context was from there but I get a "already enabled"-error. One strange thing though is that in NaviCat I see the hstore functions in the audit-schema but not in the public one. The schema concept is still a bit fuzzy for me

Pointers appreciated and thanks in advance, Nik

Nicklas Karlsson
  • 317
  • 1
  • 4
  • 12
  • `alter extension hstore set schema public;` – Abelisto Nov 10 '16 at 18:44
  • You can qualify the data type with the schema: e.g. `public hstore(old.*)` instead of `hstore(old.*)` –  Nov 10 '16 at 18:52
  • Thanks for the quick reply. I tried using `insert into audit.audit (table_name, user_name, action, old_values, new_values, updated_cols, query) values (tg_table_name::text, current_user::text, 'u', audit hstore(old.*), audit hstore(new.*), akeys(audit hstore(new.*) - audit hstore(old.*)), current_query());` (also with a . notation) but neither appear syntactically correct(?) – Nicklas Karlsson Nov 10 '16 at 19:08
  • Here is a long explanation how to find why an extension inaccessible: [http://stackoverflow.com/a/40262695/593144](http://stackoverflow.com/a/40262695/593144). Hope it would be helpful. – Abelisto Nov 10 '16 at 19:37
  • Sorry, forgot the `.` it should be `audit.hstore(old.*)` (or whatever the schema is in which the hstore extension was installed) –  Nov 11 '16 at 09:31

0 Answers0