It's kind of difficult to figure out what is going on. Apparently, the function that is called "store.add_history_master()" thinks tg_table_name is a COLUMN in a table, as evidenced by "ERROR: column "tg_table_name" does not exist"
Offhand, you probably want to assign TG_TABLE_NAME to a var and then call the function using the var. EG: DECLARE V_TABLE name := TG_TABLE_NAME; SELECT store.add_history_master($V_TABLE, .... Would you be so kind as to grace us with A. The VERSION of PostgreSQL you are working with B. The O/S you are working with. C. The complete called function IE: store.add_history_master(..) On Sun, Dec 27, 2015 at 1:27 PM, Susan Hurst <susan.hu...@brookhurstdata.com > wrote: > > 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.hu...@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 > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
