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

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

> Melvin Davidson <melvin6925@gmail.com> hat am 27. Dezember 2015 um 19:55
> geschrieben:
>
>
> 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(..)


The problem is, that tg_table_name isn't declared within a event trigger.
TG_TAG is defined, it contains the command, for instance CREATE TABLE.

And: the version is 9.4.4, as you (!) quoted it.

Please don't top-posting, it's hard to understand (as we can see here ...)


Sue: sorry, i haven't a solution, but it's an interesting question. I hope for a
solution.

Andreas Kretschmer <andreas@a-kretschmer.de> writes:
> The problem is, that tg_table_name isn't declared within a event trigger.
> TG_TAG is defined, it contains the command, for instance CREATE TABLE.

Yeah.  According to
http://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER
only TG_TAG and TG_EVENT are defined inside PL/pgSQL event triggers.

So at present, you can only do very coarse event recording using
PL/pgSQL; if you want to do anything interesting you have to resort
to writing your event trigger in C.  (And I think that even then,
9.4 did not offer very complete facilities for finding out what the
DDL command had done; 9.5 will provide more info.)

            regards, tom lane

> 9.4 did not offer very complete facilities for finding out what the
> DDL command had done; 9.5 will provide more info.)
>
>             regards, tom lane

Really?

http://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html still contains
only TG_EVENT and TG_TAG for Triggers on Events (40.9.2). If 9.5 contains more
information than should someone fix the doku.

Regards, Andreas

On 2015-12-27 13:19, Tom Lane wrote:
> Andreas Kretschmer <andreas@a-kretschmer.de> writes:
>> The problem is, that tg_table_name isn't declared within a event
>> trigger.
>> TG_TAG is defined, it contains the command, for instance CREATE TABLE.
>
> Yeah.  According to
> http://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER
> only TG_TAG and TG_EVENT are defined inside PL/pgSQL event triggers.
>
> So at present, you can only do very coarse event recording using
> PL/pgSQL; if you want to do anything interesting you have to resort
> to writing your event trigger in C.  (And I think that even then,
> 9.4 did not offer very complete facilities for finding out what the
> DDL command had done; 9.5 will provide more info.)
>
>             regards, tom lane

hmmmm...well, Tom, at least you saved me a lot of frustration with
trying to get this to work :-)  For the time being, I'll just follow up
DDL activity with a procedure that compares diffs between
information_schema and the history tables.  If and when pl/pgsql offers
the capture of DDL activity on the fly, I can just move my code to an
event trigger.

Thanks for your help!

Sue

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 12/27/2015 12:14 PM, Andreas Kretschmer wrote:
>
>> 9.4 did not offer very complete facilities for finding out what the
>> DDL command had done; 9.5 will provide more info.)
>>
>>             regards, tom lane
>
> Really?
>
> http://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html still contains
> only TG_EVENT and TG_TAG for Triggers on Events (40.9.2). If 9.5 contains more
> information than should someone fix the doku.

http://www.postgresql.org/docs/9.5/static/functions-event-triggers.html

>
> Regards, Andreas
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Susan Hurst <susan.hurst@brookhurstdata.com> wrote:

> hmmmm...well, Tom, at least you saved me a lot of frustration with
> trying to get this to work :-)  For the time being, I'll just follow up
> DDL activity with a procedure that compares diffs between
> information_schema and the history tables.  If and when pl/pgsql offers
> the capture of DDL activity on the fly, I can just move my code to an
> event trigger.

This works in 9.5:

CREATE FUNCTION test_event_trigger_for_create()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        RAISE NOTICE '% created object: %',
                     tg_tag,
                     obj.object_identity;
    END LOOP;
END
$$;
CREATE EVENT TRIGGER test_event_trigger_for_create
   ON ddl_command_end
   EXECUTE PROCEDURE test_event_trigger_for_create();


Demo:

test=*# create table foo (i int);
NOTICE:  CREATE TABLE created object: public.foo
CREATE TABLE
test=*#




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°