Triggers para que vos quero…

Triggers para que vos quero…

RidKal_TumbledownDominoes_RIK018_2_LOHá já algum tempo que queria estudar a funcionalidade de triggers em PostgreSQL. A grosso modo, tinha ideia que permitiam executar comandos de forma automática, sempre que se alterasse determinada tabela, mas desconhecia os mecanismos para o fazer. Uma das aplicações que me veio à ideia foi a de usar triggers para manter actualizado atributos geométricos como a área ou o comprimento. Quando editamos de elementos que contêm atributos relacionados com as dimensões, forma ou localização das suas geometrias (área, perímetro, comprimento), é muito fácil esquecermo-nos de os actualizar depois da edição. Se mais tarde usarmos esses atributos para realizar alguma análise, este esquecimento pode levar a resultados errados.Como exemplo, vou criar um trigger para actualizar os atributos “área”, “latitude” e “longitude” de uma tabela de polígonos.A primeira coisa a fazer é criar uma função que execute o que pretendemos. No caso em questão, usei a seguinte:

CREATE OR REPLACE FUNCTION update_geometry_fields()
<code class="sql keyword">RETURNS</code> <code class="sql keyword">trigger</code> <code class="sql keyword">AS</code>
$BODY$
DECLARE
lat_long TEXT;
BEGIN
-- Cálculo da área da geometria
NEW.area = st_area(NEW.geom);

-- Cálculo da latitude e longitude do centroíde da geometria em graus minutos e segundos
lat_long := ST_AsLatLonText(st_transform(st_centroid(NEW.geom), 4326));
NEW.latitude = split_part(lat_long,' ',1);
NEW.longitude = split_part(lat_long,' ',2);

RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

Depois, é necessário criar o trigger que despolete a função:

CREATE TRIGGER update_epvu_sgev_geom_fields
BEFORE INSERT OR UPDATE OF geom
ON epvu.sgev
FOR EACH ROW
EXECUTE PROCEDURE update_geometry_fields();

O trigger, quando lido em inglês, é bastante simples de entender. Antes de inserir uma linha nova, ou actualizar a geometria (geom) de uma linha existente da tabela “epvu.sgev”, executa a função update_geometry_fields() De notar que a criação do triggers (e respectivas funções) pode ter variações. Em primeiro lugar, os triggers podem ser despoletados antes ou depois de um INSERT, DELETE ou UPDATE numa tabela. Em segundo, podem executar a função sobre toda a tabela ou apenas nas linhas em questão. Para ilustrar estas diferenças, mais um exemplo. Com o objectivo de optimizar a impressão em mapas no QGIS, através da nova funcionalidade de atlas do print composer, criei uma consulta para agregar vários polígonos com base num atributo (“codigo”) e me determinasse se os multi-polígonos resultantes encaixava melhor numa folha ao alto ou deitado. Na tentativa de tornar o processo mais rápido, pensei em gravar a consulta como tabela mantê-la actualizada sempre que alguma alteração pertinente ocorresse através de um trigger. A função a usar no trigger era pouco mais que a consulta em si:

CREATE OR REPLACE FUNCTION epvu.refresh_geom_paginas_prestadores()
RETURNS trigger AS
$BODY$
BEGIN
TRUNCATE epvu.geom_paginas_prestadores restart identity;
INSERT INTO epvu.geom_paginas_prestadores
(codigo,
formato,
geom)
(WITH g as
(SELECT
(st_union(st_makevalid(geom))) AS geom,
codigo
FROM
epvu.sgev
GROUP BY
codigo)
SELECT
g.codigo,
CASE WHEN abs(ST_XMax(g.geom)-ST_XMin(g.geom)) >
abs(ST_YMax(g.geom)-ST_YMin(g.geom)) THEN
'Landscape'
ELSE
'Portrait'
END as formato,
st_multi(g.geom) as geom
FROM g);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

Neste caso, a função deve ser despoletada sempre que forem feitas alterações à tabela que usada na consulta (epvu.sgev) e que possam alterar os seus resultados. Assim, sempre que haja introdução (INSERT) ou eliminação (DELETE) de registos, ou que sejam alterados (UPDATE) os campos da geometria (geom) e “codigo”, o trigger executa uma única vez a função descrita acima:

CREATE TRIGGER actualiza_paginas_update
AFTER INSERT OR UPDATE OF geom, codigo OR DELETE
ON epvu.sgev
FOR EACH STATEMENT
EXECUTE PROCEDURE epvu.refresh_geom_paginas_prestadores();

Nota: Depois de alguns teste, cheguei à conclusão que, dado o número reduzido de registos que tinha, era mais rápido usar uma VIEW. Continuo no entanto a achar que, este tipo de triggers podem ser úteis para pre-processar consultas mais exigentes. Para mais informação sobre o uso de triggers, a documentação do PostgreSQL é um excelente ponto de partida.

Advertisements

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s