To identify modifications to the database structure executed by commands DDL
, use the resource of EVENT TRIGGER
of Postgresql. It is a feature that is available from version 9.3.
With it it would be possible to store in a table the data of the tables that are being created by the legacy system and later consult this data.
Create a table to store data as events occur.
create table auditoria (
id serial,
data_ocorrencia timestamp,
dados jsonb
);
Create a function that returns event_trigger
. Note the use of the function pg_event_trigger_ddl_commands()
, it will return the data of the object being modified.
CREATE or replace FUNCTION registra_evento_create()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
declare
r record;
begin
for r in
select jsonb_agg(evento) as eventos
from (
select
pg_identify_object(classid,objid,objsubid) as obj
from pg_event_trigger_ddl_commands()
) as evento
loop
execute 'insert into auditoria (data_ocorrencia, dados) values (current_timestamp,$1);'
using (r.eventos);
end loop;
end
$$;
Create a EVENT TRIGGER
. In this case it will be fired at Rigger when the command is closed and is a command CREATE TABLE
. The parameter WHEN
is not required, so it would be possible to audit other types of commands DDL
, in the documentation are indicated the options.
CREATE EVENT TRIGGER tr_evento_create
ON ddl_command_end WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE registra_evento_create();
After that each command CREATE TABLE
will trigger the function registra_evento_create()
, and with queries in the table auditoria
it would be possible to identify the tables being created by the legacy system.
Below links referring to the subject in the postgres documentation:
https://www.postgresql.org/docs/current/static/sql-createeventtrigger.html
https://www.postgresql.org/docs/current/static/event-trigger-table-rewrite-example.html
https://www.postgresql.org/docs/current/static/event-trigger-matrix.html
https://www.postgresql.org/docs/current/static/event-trigger-definition.html
https://www.postgresql.org/docs/current/static/functions-event-triggers.html
http://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-event-triggers/