Re: Calling function (table_name, schema_name) within event trigger

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(dot)hurst(at)brookhurstdata(dot)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(dot)hurst(at)brookhurstdata(dot)com
> Mobile: 314-486-3261
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.