event triggers in 9.3.4

From: "Vasudevan, Ramya" <ramya(dot)vasudevan(at)classmates(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: event triggers in 9.3.4
Date: 2014-07-24 00:22:08
Message-ID: 20EE50F73664E744AF948F0106FE6DFA585A790C@SEAMBX01.sea.corp.int.untd.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I set up the following to log all DDLs executed in the database:

CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);

CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
BEGIN
insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''', ''' || tg_event || ''', statement_timestamp())';
EXECUTE insertquery;
RAISE NOTICE 'Recorded execution of command % with event %', tg_tag, tg_event;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start EXECUTE PROCEDURE log_ddl_execution();
CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE PROCEDURE log_ddl_execution();

Is there a way to log the object name (or the oid) in the function?

Thank you,
________________
Ramya Vasudevan
Database Administrator

CLASSMATES
333 Elliott Ave. West, Suite 500
Seattle, WA 98119
206.301.4933 o

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-07-24 00:40:01 Re: event triggers in 9.3.4
Previous Message Nick Guenther 2014-07-24 00:10:41 Re: Watching Views