Calling function (table_name, schema_name) within event trigger
От | Susan Hurst |
---|---|
Тема | Calling function (table_name, schema_name) within event trigger |
Дата | |
Msg-id | 38ca6e321f946c7a0ca12422775d1878@mail.brookhurstdata.net обсуждение исходный текст |
Ответы |
Re: Calling function (table_name, schema_name) within event trigger
|
Список | 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
В списке pgsql-general по дате отправления: