What is the correct syntax for calling a function from within an event
trigger, passing in the table name and schema name as parameters to the
function?
The goal is to capture DDL changes to tables for the purpose of turning
on (or off) auditing for production tables. The history_master table
controls which tables are to be audited. I already have a procedure
that creates the trigger for an new (or altered) table that tracks DML
changes in a history table. While I would be conscientious about
including DML triggers in my tables definitions, I cannot count on
others to do so.
After I get this to work, I want to capture altered DDL as well so that
I can alter the corresponding history table with the correct column
definitions.
The following code does not work, but I think you can get the idea of
what I'm trying to accomplish. I would welcome any alternate
suggestions that you may have. I'm using version 9.4.4. on FreeBSD 8.4.
Thanks for your help!
Sue
Code:
-----
CREATE OR REPLACE FUNCTION insert_history_master()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
select store.add_history_master (tg_table_name, tg_schema_name)
;
END;
$$;
CREATE EVENT TRIGGER insert_history_master ON ddl_command_start
EXECUTE PROCEDURE insert_history_master();
Error Message:
--------------
ERROR: column "tg_table_name" does not exist
LINE 1: select store.add_history_master (tg_table_name, tg_schema_na...
^
QUERY: select store.add_history_master (tg_table_name, tg_schema_name)
CONTEXT: PL/pgSQL function insert_history_master() line 3 at SQL
statement
********** Error **********
ERROR: column "tg_table_name" does not exist
SQL state: 42703
Context: PL/pgSQL function insert_history_master() line 3 at SQL
statement
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Email: susan.hurst@xxxxxxxxxxxxxxxxxx
Mobile: 314-486-3261
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
