Event Trigger question

Started by ProPAAS DBAover 8 years ago3 messagesgeneral
Jump to latest
#1ProPAAS DBA
dba@propaas.com

Hi All;

we are creating an event trigger on ddl_command_end and I want the
function to know the TABLE and COMMAND run, for example if the ddl
command was an "ALTER TABLE ADD COLUMN X" then I want to pull the table
and the actual alter command. We're running version 9.4 so the
pg_event_trigger_ddl_commands function is not available.

I see in the 9.4 docs that the variable tg_tag is used, and I see some
references to tg_event

I assume we can pass ALTER TABLE as the filter value like so:

CREATE OR REPLACE FUNCTION trap_alter_statements() RETURNS event_trigger
LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'command %', tg_tag; END;
$$; CREATE EVENT TRIGGER abort_ddl ON ddl_command_start WHEN TAG IN
'ALTER TABLE' EXECUTE PROCEDURE trap_alter_statements(); Questions: 1)
is the above "WHEN TAG IN 'ALTER TABLE'" correct? 2) where can I find a
complete list of the tg_ variables? I see this list:
https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html which
includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the
list. Are there other variables I can reference? 3) which specific
variable will show me (a) the full command run and (b) the table name?
Thanks in advance

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: ProPAAS DBA (#1)
Re: Event Trigger question

On Thursday, July 13, 2017, ProPAAS DBA <dba@propaas.com> wrote:

2) where can I find a complete list of the tg_ variables? I see this list:https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html

which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the list. Are there other variables I can reference?

They are listed at the bottom of the page you linked to.

David J.

#3ProPAAS DBA
dba@propaas.com
In reply to: David G. Johnston (#2)
Re: Event Trigger question

Oh duh, I'm blind... Thanks!

Show quoted text

On 07/13/2017 07:29 PM, David G. Johnston wrote:

On Thursday, July 13, 2017, ProPAAS DBA <dba@propaas.com
<mailto:dba@propaas.com>> wrote:

2) where can I find a complete list of the tg_ variables? I see
this list:
https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html
<https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html&gt;
which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are
not in the list. Are there other variables I can reference?

They are listed at the bottom of the page you linked to.

David J.