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"
"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, ....
On Sun, Dec 27, 2015 at 1:27 PM, Susan Hurst <susan.hurst@xxxxxxxxxxxxxxxxxx> 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.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
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
