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.hurst@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.hurst@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.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general