Re: Calling function (table_name, schema_name) within event trigger - Mailing list pgsql-general

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@brookhurstdata.com
Mobile: 314-486-3261


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general