Event Triggers no PostgreSQL *ative triggers quando uma tabela é criada, atualizada ou apagada*
Event triggers são triggers ativadas nos casos de: criação/atualização/remoção de tabelas, funções (user defined functions), tipos (user defined types), etc. Em outras palavras, são triggers ativadas por comandos DDL (Data Definition Language), em outras palavras, comandos CREATE, ALTER e DROP.
Com as Event Triggers você pode rodar um script ou SQL quando uma tabela nova for criada ou apagada.
As Event Triggers são ativadas a nível de Banco de Dados, ou seja, qualquer operação DDL que ocorrer dentro do banco de dados chamará a trigger.
Exemplo de Event Trigger
A Event Trigger tem mais ou menos essa cara (documentação aqui):
CREATE EVENT TRIGGER event_trigger_teste ON ddl_command_startEXECUTE FUNCTION function_name() ; /* Ou segundo a documentação oficial */ CREATE EVENT TRIGGER name ON event [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ] EXECUTE { FUNCTION | PROCEDURE } function_name() ;
E para que a Even Trigger funcione, você precisa ter uma função previamente criada, note o RETURNS, é do tipo event_trigger:
CREATE FUNCTION function_name()RETURNS event_trigger AS BEGIN -- logic END; LANGUAGE language_name;
As event triggers podem ser ativadas em 4 tipos de eventos:
- ddl_command_start
- ddl_command_end
- table_rewrite
- sql_drop
Explicarei abaixo quais são esses 4 eventos, mas se quiser conferir a documentação oficial, fique à vontade.
E as limitações das Event Triggers?
As event triggers não podem ser disparadas em eventos envolvendo databases, roles, tablespaces ou as próprias event triggers (esse grupo de eventos são chamados "objetos compartilhados", "shared objects").
Em que ordem as triggers são chamadas?
As event triggers para um mesmo evento são disparadas em ordem alfabética.
Evento ddl_command_start
É chamado logo após os comandos:
CREATE, SELECT INTO, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT ou REVOKE.
É possível definir um WHEN, ativando a trigger apenas após algum comando específico.
Evento ddl_command_end
É chamado num momento antes do final da transação, após os mesmos comandos do ddl_command_start serem executados (as tabelas do system_catalog já foram alteradas nesse momento).
Os comandos executados podem ser vistos através da função pg_event_trigger_ddl_commands()
SELECT *FROM pg_event_trigger_ddl_commands();
Retorna:
| Name | Type | Description |
|---|---|---|
classid | oid | OID of catalog the object belongs in |
objid | oid | OID of the object itself |
objsubid | integer | Sub-object ID (e.g. attribute number for a column) |
command_tag | text | Command tag |
object_type | text | Type of the object |
schema_name | text | Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied. |
object_identity | text | Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
in_extension | bool | True if the command is part of an extension script |
command | pg_ddl_command | A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command. Ao tentar usar esse comando normalmente ele irá retornar a exceção: ERROR: cannot output a value of type pg_ddl_command |
Abaixo vai um exemplo completo de criação de event trigger.
Criando a função:
CREATE OR REPLACE FUNCTION funcao_mostrar_alteracoes()RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE ' Event trigger ddl_command_end detected! Operation: % Class id: % Object id: % Object Sub id: % Command tag: % Object type: % Schema: % Object identity: % In extension: %', tg_tag, obj.classid, obj.objid, obj.objsubid, obj.command_tag, obj.object_type, obj.schema_name, obj.object_identity, obj.in_extension ; END LOOP; END $$;
Criando a event trigger:
CREATE EVENT TRIGGER trigger_atualizar_views ON ddl_command_endEXECUTE FUNCTION funcao_mostrar_alteracoes();
Ao rodar um ALTER TABLE ADD COLUMN recebemos:
NOTICE:Event trigger ddl_command_end detected! Operation: ALTER TABLE Class id: 1259 Object id: 33737 Object Sub id: 0 Command tag: ALTER TABLE Object type: table Schema: public Object identity: public."unit-monitor-1" In extension: f
Evento sql_drop
É chamado por qualquer operação que apague objetos do banco de dados, antes do evento ddl_command_end.
Para visualizar quais objetos foram apagados, chamara a função do catálogo:
SELECT *FROM pg_event_trigger_dropped_objects();
Retorna:
| Name | Type | Description |
|---|---|---|
classid | oid | OID of catalog the object belonged in |
objid | oid | OID of the object itself |
objsubid | integer | Sub-object ID (e.g. attribute number for a column) |
original | bool | True if this was one of the root object(s) of the deletion |
normal | bool | True if there was a normal dependency relationship in the dependency graph leading to this object |
is_temporary | bool | True if this was a temporary object |
object_type | text | Type of the object |
schema_name | text | Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied. |
object_name | text | Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified. |
object_identity | text | Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
address_names | text[] | An array that, together with object_type and address_args, can be used by the pg_get_object_address() function to recreate the object address in a remote server containing an identically named object of the same kind |
address_args | text[] | Complement for address_names |
The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.28). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.
Evento table_rewrite
É chamado antes de uma tabela ser reescrita por um comando ALTER TABLE e ALTER TYPE. Não é chamada pelos comandos CLUSTER e VACUUM.
Em um table_rewrite, é possível chamar:
| Name | Return Type | Description |
|---|---|---|
pg_event_trigger_table_rewrite_oid() | Oid | The OID of the table about to be rewritten. |
pg_event_trigger_table_rewrite_reason() | int | The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent. |
Referências
Definição de Event Trigger
https://www.postgresql.org/docs/current/event-trigger-definition.html
Como ativar trigger após uma tabela ser criada?
https://stackoverflow.com/questions/39833466/get-table-name-in-event-trigger
Documentação Oficial - Event Triggers
https://www.postgresql.org/docs/current/event-triggers.html
Documentação Oficial - Event Trigger Functions
https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS
Exemplos
Exemplo de Event Trigger para:
Dado que ocorreu um CREATE TABLE ou ALTER TABLE em uma tabela com determinado padrão em seu nome (regex):
- Criar ou atualizar uma VIEW com base na tabela criada.
- Fazer INSERT em em uma determinada tabela com o nome das colunas não presentes na tabela
CREATE OR REPLACE FUNCTION unit_monitor.update_unit_monitor_column_definitions()RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj RECORD; tb_name TEXT; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP IF (obj.command_tag = 'CREATE TABLE' OR obj.command_tag = 'ALTER TABLE') AND obj.object_identity ~ '"?unit_monitor"?\."?unit_monitor(_[0-9]+)+"?$' THEN tb_name := (regexp_match(obj.object_identity, '"?unit_monitor"?\."?(unit_monitor(_[0-9]+)+)"?$'))[1]; RAISE INFO 'CREATE TABLE or ALTER TABLE detected on %.%.', obj.schema_name, tb_name; -- Deletar View EXECUTE format('DROP VIEW IF EXISTS unit_monitor.%I ;', tb_name||'_columns'); -- Criar View EXECUTE format(' CREATE OR REPLACE VIEW unit_monitor.%I AS SELECT tb.table_schema AS schema, tb.table_name AS "table", tb.column_name AS "column", tb.data_type, COALESCE(cd."pt-BR", tb.column_name::text) AS "pt-BR", COALESCE(cd."en-US", tb.column_name::text) AS "en-US", COALESCE(cd.is_categorical, false) AS is_categorical, COALESCE(cd.is_main_timestamp, false) AS is_main_timestamp, COALESCE(cd.is_visible_at_birth_certificate, false) AS is_visible_at_birth_certificate, CASE WHEN tco.constraint_type::text = ''PRIMARY KEY''::text THEN true ELSE false END AS is_primary_key, kcu.ordinal_position, tco.constraint_type FROM information_schema.columns tb LEFT JOIN unit_monitor.unit_monitor_column_definition cd ON tb.table_schema::text = cd.schema AND tb.table_name::text = cd."table" AND tb.column_name::text = cd."column" LEFT JOIN information_schema.key_column_usage kcu ON kcu.table_schema::text = cd.schema AND kcu.table_name::text = cd."table" AND kcu.column_name::text = cd."column" LEFT JOIN information_schema.table_constraints tco ON kcu.constraint_name::text = tco.constraint_name::text AND kcu.constraint_schema::text = tco.constraint_schema::text AND tco.constraint_type::text = ''PRIMARY KEY''::text WHERE tb.table_name::text = %L::text; ',tb_name||'_columns',tb_name); INSERT INTO unit_monitor.unit_monitor_column_definition SELECT tb.table_schema::TEXT AS schema, tb.table_name::TEXT AS "table", tb.column_name::TEXT AS "column" FROM information_schema.columns tb LEFT JOIN unit_monitor.unit_monitor_column_definition umc ON umc.schema = tb.table_schema AND umc.table = tb.table_name AND umc.column = tb.column_name WHERE tb.table_schema = obj.schema_name AND tb.table_name = tb_name AND umc.column IS NULL; END IF; END LOOP; END $$; CREATE EVENT TRIGGER update_unit_monitor_column_definitions ON DDL_COMMAND_END EXECUTE PROCEDURE unit_monitor.update_unit_monitor_column_definitions();