Postgresql - Triggers on DDL Statements

PostgreSQL 9.3

Awaiting DDL Triggers i.e EVENT TRIGGERS  features  finally  come live with PostgreSQL 9.3

Yes!!.. now we can have a Trigger on DDL Statements like Oracle does.

Syntax                                                                                                                               

CREATE - EVENT TRIGGER 

CREATE EVENT TRIGGER trigger_name
  ON event
  [ WHEN condition IN (values)  ]
  EXECUTE PROCEDURE function_name()

event : ddl_command_start, ddl_command_end and sql_drop 

ddl_command_start

 Trigger function ( i.e function_name() ) would be called before execution of CREATE/ALTER/DROP command.


ddl_command_end

Trigger function ( i.e function_name() ) would be called after execution  of CREATE/ALTER/DROP command.


sql_drop 

Trigger function ( i.e function_name() ) would be called Just before "ddl_command_end" event triggered.
Only for the DROP commands
To view the list of objects deleted by pg_event_trigger_dropped_objects()

condition : tg_tag. ( It will return calling trigger command ) 
values : CREATE TABLE, CREATE FUNCTION complete list of values 
function_name : A normal pl/pgsql function with return type as event_trigger & it not needed to return any value.

Ex:
CREATE TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION fn_ddl_trigger()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'Current running DDL command is : % ', tg_tag;
END;
$$;

CREATE EVENT TRIGGER 1)  Simple  

CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start
   EXECUTE PROCEDURE fn_ddl_trigger();

OUTPUT 1)

root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table1 (empname varchar(200));
NOTICE:  Current running DDL command is: CREATE TABLE
CREATE TABLE

CREATE EVENT TRIGGER 2) With 'WHEN' condition
      

CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start WHEN tg_tag in (ALTER_TABLE, DROP_TABLE)
EXECUTE PROCEDURE fn_ddl_trigger();

OUTPUT 2)  trigger function will get called only ALTER and DROP, not for CREATE TABLE, so no messgae to display.

root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table2 (empname varchar(200));

CREATE TABLE

ALTER - EVENT TRIGGER

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO new_owner
ALTER EVENT TRIGGER name RENAME TO new_name

DROP - EVENT TRIGGER

DROP EVENT TRIGGER [ IF EXISTS ] name