>>You could also use event triggers:
https://www.postgresql.org/docs/current/static/event-triggers.html
Yes, But can I get the exact text so executed into a table. Please consider
the below example which clearly explains my requirement:
Step1: Created a table like below:
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
Step2: Created a function like below:
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;
Step3: Created event triggers as below:
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();
Step4: Triggered a DDL statement as:
create table aa (a int);
Finally issued select on the ddl history table:
select * from log_ddl_info;
ddl_tag | ddl_event | ddl_time
--------------+-------------------+-------------------------
CREATE TABLE | ddl_command_start | 2018-08-28 18:47:55.745
CREATE TABLE | ddl_command_end | 2018-08-28 18:47:55.745
But from the above output I also need the exact DDL statement text and which
db user triggered it. Is it possible??
Looking forward to hear from you!
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html