CREATE SEQUENCE public.log_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.log_seq
  OWNER TO postgres;

DROP TABLE public.tbl_create_log
CREATE TABLE public.tbl_create_log
(
  log_id  bigint NOT NULL DEFAULT nextval('log_seq'::regclass),
  log_table_schema name NOT NULL,
  log_table_name name NOT NULL,
  log_table_cre8_time timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
  CONSTRAINT tbl_create_log_pk PRIMARY KEY (log_table_schema, log_table_name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_create_log
  OWNER TO postgres;


-- Function: public.fn_notify_ddl()

-- DROP FUNCTION public.fn_notify_ddl();

CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
  RETURNS event_trigger AS
$BODY$
DECLARE
	obj record;
BEGIN

    FOR obj IN SELECT * 
                 FROM pg_event_trigger_ddl_commands() LOOP
	IF obj.command_tag = 'CREATE TABLE' THEN
          RAISE INFO 'we got a % event for object "%"', obj.command_tag, obj.object_identity;
          INSERT INTO tbl_create_log
          (log_table_schema, log_table_name)
          SELECT n.nspname, c.relname
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE c.oid = obj.objid
             AND c.relkind = 'r';
        END IF;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.fn_notify_ddl()
  OWNER TO postgres;


CREATE EVENT TRIGGER tg_event_audit_all ON ddl command end 
  EXECUTE PROCEDURE public.fn_notify_ddl();  
  
 -- Now test the event & trigger function
CREATE TABLE public.sneaky_pete
(
  pk_col character varying(5) NOT NULL,
  col2 character varying(10),
  CONSTRAINT sneaky_pete_pk PRIMARY KEY (pk_col)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.sneaky_pete
  OWNER TO postgres;