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  (Melvin Davidson <melvin6925@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: grep -f keyword data query
Следующее
От: Tom Lane
Дата:
Сообщение: Re: grep -f keyword data query