>>-----Исходное сообщение----- >>От: Laurenz Albe <laurenz.a...@cybertec.at> >>Отправлено: 22 февраля 2023 г. 12:52 >>Кому: Neethu P <neet...@hotmail.com>; pgsql-general >><pgsql-gene...@postgresql.org> >>Тема: Re: Event Triggers unable to capture the DDL script executed
>>On Wed, 2023-02-22 at 07:57 +0000, Neethu P wrote: >>> We are using event triggers to capture the DDL changes in a postgres >>> database. >>> However, we are unable to get the column information & the actual DDL >>> script executed, while a table is altered. >>> Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- >>> it is mentioned as below. >>> pg_ddl_command A complete representation of the command, in internal >>> format. Thiscannot be output directly, but it can be passed to other >>> functions to obtain different pieces of information about the command. >>> >>> Is it possible to access pg_ddl_command in postgresql? Or is there any >>> scripts which can help to get theactual Alter DDL statement that was >>> executed by the user? >>That is simple if you write the event trigger in C. I would say that that is >>the only way to get at the actual statement. >>Yours, >>Laurenz Albe In MSSQL there is a brilliant possibility to have a server wide trigger to monitor commands. We are using It to have a history for all DDL operations. Please try this (on new empty database) and give a feedback. CREATE OR REPLACE FUNCTION public.notice_ddl() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'we got a % event for object " %"', r.command_tag, r.object_identity; END LOOP; end; $BODY$; CREATE OR REPLACE FUNCTION public.notice_ddl_drop() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP RAISE NOTICE 'dropped: type "%" identity %', r.object_type, r.object_identity; END LOOP; end; $BODY$; CREATE EVENT TRIGGER etg ON DDL_COMMAND_END EXECUTE PROCEDURE public.notice_ddl(); CREATE EVENT TRIGGER etg_drop ON SQL_DROP EXECUTE PROCEDURE public.notice_ddl_drop();