| Documentação do PostgreSQL 8.0.0 | ||||
|---|---|---|---|---|
| Anterior | Início | Capítulo 35. PL/pgSQL - Linguagem procedural SQL | Fim | Próxima |
A linguagem PL/pgSQL pode ser utilizada para definir procedimentos de gatilho. O procedimento de gatilho é criado pelo comando CREATE FUNCTION, declarando o procedimento como uma função sem argumentos e que retorna o tipo trigger. Deve ser observado que a função deve ser declarada sem argumentos, mesmo que espere receber os argumentos especificados no comando CREATE TRIGGER — os argumentos do gatilho são passados através de TG_ARGV, conforme descrito abaixo.
Quando uma função escrita em PL/pgSQL é chamada como um gatilho, diversas variáveis especiais são criadas automaticamente no bloco de nível mais alto. São estas:
Tipo de dado RECORD; variável contendo a nova linha do banco de dados, para as operações de INSERT/UPDATE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.
Tipo de dado RECORD; variável contendo a antiga linha do banco de dados, para as operações de UPDATE/DELETE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.
Tipo de dado name; variável contendo o nome do gatilho disparado.
Tipo de dado text; uma cadeia de caracteres contendo BEFORE ou AFTER, dependendo da definição do gatilho.
Tipo de dado text; uma cadeia de caracteres contendo ROW ou STATEMENT, dependendo da definição do gatilho.
Tipo de dado text; uma cadeia de caracteres contendo INSERT, UPDATE, ou DELETE, informando para qual operação o gatilho foi disparado.
Tipo de dado oid; o ID de objeto da tabela que causou o disparo do gatilho.
Tipo de dado name; o nome da tabela que causou o disparo do gatilho.
Tipo de dado integer; o número de argumentos fornecidos ao procedimento de gatilho na instrução CREATE TRIGGER.
Tipo de dado matriz de text; os argumentos da instrução CREATE TRIGGER. O contador do índice começa por 0. Índices inválidos (menor que 0 ou maior ou igual a tg_nargs) resultam em um valor nulo.
Uma função de gatilho deve retornar nulo, ou um valor registro/linha possuindo a mesma estrutura da tabela para a qual o gatilho foi disparado.
Os gatilhos no nível de linha disparados BEFORE (antes) podem retornar nulo, para sinalizar ao gerenciador do gatilho para pular o restante da operação para esta linha (ou seja, os gatilhos posteriores não serão disparados, e não ocorrerá o INSERT/UPDATE/DELETE para esta linha. Se for retornado um valor diferente de nulo, então a operação prossegue com este valor de linha. Retornar um valor de linha diferente do valor original de NEW altera a linha que será inserida ou atualizada (mas não tem efeito direto no caso do DELETE). Para alterar a linha a ser armazenada, é possível substituir valores individuais diretamente em NEW e retornar o NEW modificado, ou construir um novo registro/linha completo a ser retornado.
O valor retornado por um gatilho BEFORE ou AFTER no nível de instrução, ou por um gatilho AFTER no nível de linha, é sempre ignorado; pode muito bem ser nulo. Entretanto, qualquer um destes tipos de gatilho pode interromper toda a operação gerando um erro.
O Exemplo 35-1 mostra um exemplo de procedimento de gatilho escrito em PL/pgSQL.
Exemplo 35-1. Procedimento de gatilho PL/pgSQL
O gatilho deste exemplo garante que quando é inserida ou atualizada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a atualização, e quando isto ocorreu. Além disso, o gatilho verifica se é fornecido o nome do empregado, e se o valor do salário é um número positivo.
CREATE TABLE emp (
nome_emp text,
salario integer,
ultima_data timestamp,
ultimo_usuario text
);
CREATE FUNCTION emp_gatilho() RETURNS trigger AS $emp_gatilho$
BEGIN
-- Verificar se foi fornecido o nome e o salário do empregado
IF NEW.nome_emp IS NULL THEN
RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
END IF;
IF NEW.salario IS NULL THEN
RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome_emp;
END IF;
-- Quem paga para trabalhar?
IF NEW.salario < 0 THEN
RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome_emp;
END IF;
-- Registrar quem alterou a folha de pagamento e quando
NEW.ultima_data := 'now';
NEW.ultimo_usuario := current_user;
RETURN NEW;
END;
$emp_gatilho$ LANGUAGE plpgsql;
CREATE TRIGGER emp_gatilho BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_gatilho();
INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);
SELECT * FROM emp;
nome_emp | salario | ultima_data | ultimo_usuario
----------+---------+----------------------------+----------------
João | 1000 | 2005-11-25 07:07:50.59532 | folha
José | 1500 | 2005-11-25 07:07:50.691905 | folha
Maria | 2500 | 2005-11-25 07:07:50.694995 | folha
(3 linhas)
Exemplo 35-2. Procedimento de gatilho PL/pgSQL para registrar inserção e atualização
O gatilho deste exemplo garante que quando é inserida ou atualizada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a atualização, e quando isto ocorreu. Porém, diferentemente do gatilho anterior, a criação e a atualização da linha são registradas em colunas diferentes. Além disso, o gatilho verifica se é fornecido o nome do empregado, e se o valor do salário é um número positivo. [1]
CREATE TABLE emp (
nome_emp text,
salario integer,
usu_cria text, -- Usuário que criou a linha
data_cria timestamp, -- Data da criação da linha
usu_atu text, -- Usuário que fez a atualização
data_atu timestamp -- Data da atualização
);
CREATE FUNCTION emp_gatilho() RETURNS trigger AS $emp_gatilho$
BEGIN
-- Verificar se foi fornecido o nome do empregado
IF NEW.nome_emp IS NULL THEN
RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
END IF;
IF NEW.salario IS NULL THEN
RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome_emp;
END IF;
-- Quem paga para trabalhar?
IF NEW.salario < 0 THEN
RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome_emp;
END IF;
-- Registrar quem criou a linha e quando
IF (TG_OP = 'INSERT') THEN
NEW.data_cria := current_timestamp;
NEW.usu_cria := current_user;
-- Registrar quem alterou a linha e quando
ELSIF (TG_OP = 'UPDATE') THEN
NEW.data_atu := current_timestamp;
NEW.usu_atu := current_user;
END IF;
RETURN NEW;
END;
$emp_gatilho$ LANGUAGE plpgsql;
CREATE TRIGGER emp_gatilho BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_gatilho();
INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';
SELECT * FROM emp;
nome_emp | salario | usu_cria | data_cria | usu_atu | data_atu
----------+---------+----------+----------------------------+---------+----------------------------
João | 1000 | folha | 2005-11-25 08:11:40.63868 | |
José | 1500 | folha | 2005-11-25 08:11:40.674356 | |
Maria | 2500 | folha | 2005-11-25 08:11:40.679592 | folha | 2005-11-25 08:11:40.682394
(3 linhas)
Uma outra maneira de registrar as modificações na tabela envolve a criação de uma nova tabela contendo uma linha para cada inserção, atualização ou exclusão que ocorra. Esta abordagem pode ser considerada como uma auditoria das mudanças na tabela. O Exemplo 35-3 mostra um procedimento de gatilho de auditoria em PL/pgSQL.
Exemplo 35-3. Procedimento de gatilho PL/pgSQL para auditoria
Este gatilho garante que todas as inserções, atualizações e exclusões de linha na tabela emp são registradas na tabela emp_audit, para permitir auditar as operações efetuadas na tabela emp. O nome de usuário e a hora corrente são gravadas na linha, junto com o tipo de operação que foi realizada.
CREATE TABLE emp (
nome_emp text NOT NULL,
salario integer
);
CREATE TABLE emp_audit(
operacao char(1) NOT NULL,
usuario text NOT NULL,
data timestamp NOT NULL,
nome_emp text NOT NULL,
salario integer
);
CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Cria uma linha na tabela emp_audit para refletir a operação
-- realizada na tabela emp. Utiliza a variável especial TG_OP
-- para descobrir a operação sendo realizada.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'E', user, now(), OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'A', user, now(), NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', user, now(), NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$emp_audit$ language plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();
INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';
DELETE FROM emp WHERE nome_emp = 'João';
SELECT * FROM emp;
nome_emp | salario
----------+---------
José | 1500
Maria | 2500
(2 linhas)
SELECT * FROM emp_audit;
operacao | usuario | data | nome_emp | salario
----------+---------+----------------------------+----------+---------
I | folha | 2005-11-25 09:06:03.008735 | João | 1000
I | folha | 2005-11-25 09:06:03.014245 | José | 1500
I | folha | 2005-11-25 09:06:03.049443 | Maria | 250
A | folha | 2005-11-25 09:06:03.052972 | Maria | 2500
E | folha | 2005-11-25 09:06:03.056774 | João | 1000
(5 linhas)
Exemplo 35-4. Procedimento de gatilho PL/pgSQL para auditoria no nível de coluna
Este gatilho registra todas as atualizações realizadas nas colunas nome_emp e salario da tabela emp na tabela emp_audit (isto é, as colunas são auditadas). O nome de usuário e a hora corrente são registrados junto com a chave da linha (id) e a informação atualizada. Não é permitido atualizar a chave da linha. Este exemplo difere do anterior pela auditoria ser no nível de coluna, e não no nível de linha. [2]
CREATE TABLE emp (
id serial PRIMARY KEY,
nome_emp text NOT NULL,
salario integer
);
CREATE TABLE emp_audit(
usuario text NOT NULL,
data timestamp NOT NULL,
id integer NOT NULL,
coluna text NOT NULL,
valor_antigo text NOT NULL,
valor_novo text NOT NULL
);
CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Não permitir atualizar a chave primária
--
IF (NEW.id <> OLD.id) THEN
RAISE EXCEPTION 'Não é permitido atualizar o campo ID';
END IF;
--
-- Inserir linhas na tabela emp_audit para refletir as alterações
-- realizada na tabela emp.
--
IF (NEW.nome_emp <> OLD.nome_emp) THEN
INSERT INTO emp_audit SELECT current_user, current_timestamp,
NEW.id, 'nome_emp', OLD.nome_emp, NEW.nome_emp;
END IF;
IF (NEW.salario <> OLD.salario) THEN
INSERT INTO emp_audit SELECT current_user, current_timestamp,
NEW.id, 'salario', OLD.salario, NEW.salario;
END IF;
RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$emp_audit$ language plpgsql;
CREATE TRIGGER emp_audit
AFTER UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();
INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);
UPDATE emp SET salario = 2500 WHERE id = 2;
UPDATE emp SET nome_emp = 'Maria Cecília' WHERE id = 3;
UPDATE emp SET id=100 WHERE id=1;
ERRO: Não é permitido atualizar o campo ID
SELECT * FROM emp;
id | nome_emp | salario
----+---------------+---------
1 | João | 1000
2 | José | 2500
3 | Maria Cecília | 2500
(3 linhas)
SELECT * FROM emp_audit;
usuario | data | id | coluna | valor_antigo | valor_novo
---------+----------------------------+----+----------+--------------+---------------
folha | 2005-11-25 12:21:08.493268 | 2 | salario | 1500 | 2500
folha | 2005-11-25 12:21:08.49822 | 3 | nome_emp | Maria | Maria Cecília
(2 linhas)
Uma das utilizações de gatilho é para manter uma tabela contendo o sumário de outra tabela. O sumário produzido pode ser utilizado no lugar da tabela original em diversas consultas — geralmente com um tempo de execução bem menor. Esta técnica é muito utilizada em Armazém de Dados (Data Warehousing), onde as tabelas dos dados medidos ou observados (chamadas de tabelas fato) podem ser muito grandes. O Exemplo 35-5 mostra um procedimento de gatilho em PL/pgSQL para manter uma tabela de sumário de uma tabela fato em um armazém de dados.
Exemplo 35-5. Procedimento de gatilho PL/pgSQL para manter uma tabela sumário
O esquema que está detalhado a seguir é parcialmente baseado no exemplo Grocery Store do livro The Data Warehouse Toolkit de Ralph Kimball.
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Update the summary row with the new values.
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
-- There might have been no row with this time_key (e.g new data!).
IF (NOT FOUND) THEN
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXCEPTION
--
-- Catch race condition when two transactions are adding data
-- for a new time_key.
--
WHEN UNIQUE_VIOLATION THEN
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
END;
END IF;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
Exemplo 35-6. Procedimento de gatilho para controlar sobreposição de datas
O gatilho deste exemplo verifica se o compromiso sendo agendado ou modificado se sobrepõe a outro compromisso já agendado. Se houver sobreposição, emite mensagem de erro e não permite a operação. [3]
Abaixo está mostrado o script utilizado para criar a tabela, a função de gatilho e os gatilhos de inserção e atualização.
CREATE TABLE agendamentos (
id SERIAL PRIMARY KEY,
nome TEXT,
evento TEXT,
data_inicio TIMESTAMP,
data_fim TIMESTAMP
);
CREATE FUNCTION fun_verifica_agendamentos() RETURNS "trigger" AS
$fun_verifica_agendamentos$
BEGIN
/* Verificar se a data de início é maior que a data de fim */
IF NEW.data_inicio > NEW.data_fim THEN
RAISE EXCEPTION 'A data de início não pode ser maior que a data de fim';
END IF;
/* Verificar se há sobreposição com agendamentos existentes */
IF EXISTS (
SELECT 1
FROM agendamentos
WHERE nome = NEW.nome
AND ((data_inicio, data_fim) OVERLAPS
(NEW.data_inicio, NEW.data_fim))
)
THEN
RAISE EXCEPTION 'impossível agendar - existe outro compromisso';
END IF;
RETURN NEW;
END;
$fun_verifica_agendamentos$ LANGUAGE plpgsql;
COMMENT ON FUNCTION fun_verifica_agendamentos() IS
'Verifica se o agendamento é possível';
CREATE TRIGGER trg_agendamentos_ins
BEFORE INSERT ON agendamentos
FOR EACH ROW
EXECUTE PROCEDURE fun_verifica_agendamentos();
CREATE TRIGGER trg_agendamentos_upd
BEFORE UPDATE ON agendamentos
FOR EACH ROW
EXECUTE PROCEDURE fun_verifica_agendamentos();
Abaixo está mostrado um exemplo de utilização do gatilho. Deve ser observado que os intervalos ('2005-08-23 14:00:00', '2005-08-23 15:00:00') e ('2005-08-23 15:00:00', '2005-08-23 16:00:00') não se sobrepõem, uma vez que o primeiro intervalo termina às quinze horas, enquanto o segundo intervalo inicia às quinze horas, estando, portanto, o segundo intervalo imediatamente após o primeiro.
=> INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Congresso','2005-08-23','2005-08-24'); => INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Viagem','2005-08-24','2005-08-26'); => INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Palestra','2005-08-23','2005-08-26'); ERRO: impossível agendar - existe outro compromisso => INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Cabeleireiro','2005-08-23 14:00:00','2005-08-23 15:00:00'); => INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Manicure','2005-08-23 15:00:00','2005-08-23 16:00:00'); => INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Médico','2005-08-23 14:30:00','2005-08-23 15:00:00'); ERRO: impossível agendar - existe outro compromisso => UPDATE agendamentos SET data_inicio='2005-08-24' WHERE id=2; ERRO: impossível agendar - existe outro compromisso => SELECT * FROM agendamentos; id | nome | evento | data_inicio | data_fim ----+-------+--------------+---------------------+--------------------- 1 | Joana | Congresso | 2005-08-23 00:00:00 | 2005-08-24 00:00:00 2 | Joana | Viagem | 2005-08-24 00:00:00 | 2005-08-26 00:00:00 4 | Maria | Cabeleireiro | 2005-08-23 14:00:00 | 2005-08-23 15:00:00 5 | Maria | Manicure | 2005-08-23 15:00:00 | 2005-08-23 16:00:00 (4 linhas)
| [1] |
Exemplo escrito pelo tradutor, não fazendo parte do manual original. |
| [2] |
Exemplo escrito pelo tradutor, não fazendo parte do manual original. |
| [3] |
Exemplo escrito pelo tradutor, não fazendo parte do manual original, baseado em exemplo da lista de discussão pgsql-sql. |
| Anterior | Principal | Próxima |
| Erros e mensagens | Acima | Conversão do PL/SQL do Oracle para o PL/pgSQL do PostgreSQL |
