Ubuntu 9.10

PostgreSQL

Quando temos um sistema comercial, independentemente do que ele faça sempre nos vem uma palavra à cabeça: “desempenho”. Imagine um sistema que é distribuído em muitas estações através de VPN ou TS, em um horário critico, o quanto de tempo poderia levar para inserir ou apagar um simples registro em uma tabela.

Como garantir a integridade dos dados em nossa base e ao mesmo tempo deixar o banco rápido, sem precisar colocar no sistema inúmeras verificações para validar os dados? Fácil, podemos utilizar alguns dos recursos que o banco de dados nos oferece como os que vou mostrar durante este artigo.

Neste artigo, vou falar um pouco sobre rule, trigger e function e o quanto eles podem ajudar no sentido de manter um bom desempenho e a integridade dos dados, inclusive deixando o código fonte do sistema menor e de fácil manutenção.

Para o exemplo

Para este artigo vamos imaginar a seguinte situação: uma empresa que trabalha com entrega de correspondência e produtos, uma espécie de “door to door”. Essa empresa usa um sistema de leitura para manter o controle da correspondência até seu destino final.

Exemplo: Um produto sai da filial de São Paulo, com destino ao Espírito Santo, só que antes de ir para ES, ele passa pelo Rio de Janeiro. Ou seja, o produto antes de chegar ao seu destino final vai ser registrado três vezes como mostram a figura 1.

Figura 1. Diagrama do processo de leitura de correspondência.

Imagine também que em cada passagem do produto, é tirado um relatório de romaneio, que é entregue ao motorista que leva o produto para seu destino final. Esse relatório de romaneio é tirado a partir de um número de lote que o próprio sistema gera.

A partir dessas informações, será criado uma espécie de “rastreamento” desse produto. Para fazer isto, imagine duas tabelas, uma tabela principal, onde vai ser registrada a primeira passagem do produto, e uma tabela de passagens, onde entrarão as demais passagens até o seu destino final.

Agora vem a questão: Como fazer para saber quando é a primeira passagem do produto e quando são as demais?

Para esta pergunta existem duas sugestões:

1. Colocar comandos SQL dentro do sistema, checando se o produto existe ou não na tabela principal. Se existir, gravar na tabela de passagem, se não existir, gravar na tabela principal e depois na de passagem (Optei por gravar a primeira passagem na tabela de passagens também, pois ai deixa minha tabela principal somente para gerar os relatórios de romaneios).

2. Criar regras no banco de dados para verificar se o produto existe ou não, e na própria regra fazer com que os dados sejam distribuídos nas tabelas.

Entre as duas sugestões, pela lógica, vamos optar pela segunda, pois assim vai haver um desempenho maior tanto no banco de dados como no sistema. E futuramente, mudando a plataforma do sistema, não vai ser preciso mexer no banco de dados.

Estrutura do banco
O banco de dados possui tabelas de:
LOTE, todos os produtos são lidos a partir de um número de lote.
EMPRESA, a empresa que esta lendo o malote (caso haja mais de uma empresa usando o mesmo sistema).
ORIGEM e DESTINO, ex: 2100 – SP CAPITAL para 2399 – RJ CAPITAL (Uso a mesma tabela para origem e destino).
LEITURA, onde fica registrada a passagem do malote.

Para o exemplo, vou usar esta estrutura, alterando o nome dos campos para nomes mais apropriados.
Para o primeiro tópico, vou falar sobre rule.

Rule
O comando CREATE RULE cria uma regra para ser aplicada a uma tabela ou visão específica. O comando CREATE OR REPLACE RULE cria ou substitui uma regra existente com o mesmo nome para a mesma tabela.
As RULES (regras) permitem executar uma ação alternativa em: inclusão, alteração, exclusão e seleção (somente em caso de visões).
Em outras palavras, uma regra faz com que sejam executados comandos adicionais quando é executado um determinado comando em uma tabela.
As regras são executadas antes do início da execução de um comando. Se em alguma situação você desejar uma operação que dispare um evento de forma independente para cada linha da tabela, estamos falando de um gatilho e não de uma regra.
É importante saber que as regras não fazem parte do SQL padrão, mas são implementadas pelo Postgresql.
Para o exemplo, vou criar duas tabelas, que vou chamar de tb_main e tb_hist. A tb_main será responsável pelo registro da primeira passagem do malote e a tb_hist será responsável pelas demais passagens do malote ate o destino final, como mostra a Listagem 1.

Listagem 1. Script das tabelas tb_main e tb_hist
CREATE TABLE tb_main (
codtbmain    integer not null,    – código da tabela
codtblote    integer not null,    – código do lote
codtbprod    integer not null,    – código do produto
codorigem    integer not null,    – código origem do produto
coddestno    integer not null,     – código do destino do produto
codbarras    varchar (30) not null,– código de barras
dtatbmain    date,            – data da entrada

CONSTRAINT codtbmain_pkey PRIMARY KEY (codtbmain) –- chave primaria
);

CREATE TABLE tb_hist (
codtbhist    integer not null,    – código da tabela
codtblote    integer not null,    – código do lote
codtbprod    integer not null,     – código do produto
codorigem     integer not null,    – código origem do produto
coddestno    integer not null,     – código do destino do produto
codbarras     varchar (30) not null,– código de barras
dtatbhist    date,            – data da passagem do produto
hratbhist     time,            – hora da passagem

CONSTRAINT codtbhist_pkey PRIMARY KEY (codtbhist) –- chave primaria
);

Para garantir que o produto só seja registrado uma única vez na tabela principal, é necessário criar uma unique na tabela principal (tb_main), que vai ser: código do produto (codtbprod) e o código de barras do produto (codbarras), de acordo com o código abaixo:

CREATE UNIQUE INDEX unqtb_main ON tb_main (codtbprod,codbarras);

Ou seja, o produto só será gravado na tabela principal uma única vez, as demais passagens serão registradas na tabela de histórico.
Agora vem o ponto onde quero chegar: Para não termos que fazer o sistema verificar se o produto já existe ou não na tabela principal, antes de inserirmos o registro (o que causaria perda de desempenho), podemos criar uma regra (RULE) para fazer esta verificação no próprio banco, como mostra Listagem 2.

Listagem 2. Estrutura da rule rtb_main
CREATE RULE rtb_main AS ON INSERT TO tb_main    WHERE (EXISTS
(SELECT codtbprod, codbarras FROM tb_main
WHERE codtbprod = NEW.codtbprod AND codbarras = NEW.codbarras)
) DO INSTEAD (
INSERT INTO tb_hist (
codtbhist,
codtblote,
codtbprod,
codorigem,
coddestno,
codbarras,
dtatbhist,
hratbhist
) VALUES (
SELECT NEXTVAL(‘sequence’),    – cahave (pode ser um SEQUENCE)
NEW.codtblote,            – número do lote
NEW.codtbprod,            – código do produto
NEW.codorigem,            – código da origem
NEW.coddestno,            – código do destino
NEW.codbarras,            – código de barras
(SELECT CURRENT_DATE),        – seleciona data do banco
(SELECT CURRENT_TIME)        – seleciona hora do banco
)
);

Agora, sempre que ocorrer o evento INSERT na tabela principal (tb_main), a RULE rtb_main, vai verificar se o mesmo registro já existe nesta tabela. Se existir, então vai ser executado o bloco de comando INSTEAD, o comando anterior é ignorado (inserir na tabela principal “tb_main”) e então o registro vai para a tabela de histórico (tb_hist). Se não existir, o registro será gravado na tabela principal (tb_main) e também na tabela de histórico (tb_hist), para conseguir ter um controle total do rastreamento do produto.
Quando acionamos funções em PL/pgSQL, para tratamento de dados, algumas variáveis são criadas automaticamente, como é o caso do NEW e do OLD, que veremos a seguir. Essas duas variáveis guardam os parâmetros de comandos SQL ex: INSERT INTO tabela VALUES (parametro1, parametro2…).

NEW
Usa o tipo de dado RECORD, contem o parâmetro de uma nova linha para o banco de dados, é usado somente em casos de INSERT e UPDATE.

OLD
Também é do tipo RECORD, mas contem o parâmetro de uma linha antiga do banco de dados, e é usada somente em UPDATE e DELETE.

Existem muitas outras variáveis que o Postgresql cria em funções, mas para o exemplo, vou usar apenas NEW e OLD, veja abaixo algumas variáveis.

VARIAVEIS EM POSTGRESQL
TG_WHEN Tipo de dado text; uma cadeia de caracteres contendo BEFORE ou AFTER dependendo da definição do gatilho.

TG_LEVEL Tipo de dado text; uma cadeia de caracteres contendo ROW ou STATEMENT dependendo da definição do gatilho.

TG_OP Tipo de dado text; uma cadeia de caracteres contendo INSERT, UPDATE, ou DELETE informando para qual operação o gatilho foi disparado.

TG_RELID Tipo de dado oid; o ID de objeto da tabela que causou o disparo do gatilho.
TG_NAME Tipo de dado name; variável contendo o nome do gatilho realmente disparado.

Em ambas as tabelas, existe um campo chamado codtblote, que nada mais é do que um número que identifica o lote em que o produto é inserido. Isso é para ter um controle interno (gerar relatórios, pesquisar os produtos que estão em determinado lote, etc.).
Então, já que o numero de um lote será variável (para cada passagem do produto, o sistema vai gerar um numero de lote diferente), vamos criar mais uma regra, que agora vai atualizar o numero do lote codtblote, da tabela principal tb_main.
Pois assim, conseguiremos tirar os relatórios e pesquisas com um ganho maior em desempenho, devido à quantidade menor de registros que vão existir na tabela principal, como mostra a criação da regra na Listagem 3.

Listagem 3. Estrutura da rule rtb_hist
CREATE RULE rtb_hist AS ON INSERT TO tb_hist
DO UPDATE tb_main SET codtblote = NEW.codtblote
WHERE codtbprod = NEW.codtbprod AND codbarras = NEW.codbarras

Sempre que uma nova linha for inclusa na tabela de histórico, essa RULE vai fazer com que seja atualizado o número do lote (codtblote) na tabela principal (tb_main).
Para finalizar esta parte de RULE, vamos criar uma regra para o evento DELETE, que sempre que por algum motivo, um registro precise ser apagado durante o processo de rastreamento, o registro não saia da tabela principal (tb_main), para não perder sua referencia, mas sim da tabela de histórico.
Para este exemplo, vou usar como parâmetro o lote codtblote, para deletar somente o registro que foi lido naquele lote especifico. (ver Listagem 4).

Listagem 4. Estrutura da rule rtb_hist no evento ON DELETE.
CREATE RULE rtb_hist AS ON DELETE TO tb_main
DO INSTEAD
(DELETE FROM tb_hist WHERE codtbprod = OLD.codtbprod AND codbarras = OLD.codbarras AND codtblote = OLD.codtblote);

Nesta regra, esta sendo usada a variável OLD, pois estamos usando o evento DELETE. Podemos ler mais sobre essas variáveis na documentação oficial do Postgresql, na seção Links.

TRIGGER
As Triggers (gatilhos) são recursos dos bancos de dados que, executam funções no banco, quando um determinado evento ocorrer na tabela em que a trigger estiver associada. Diferentemente das regras, os gatilhos podem ser acionados antes (before) ou depois (after) de alguma ação de insert, update e delete.
Veja a sintaxe abaixo:

CREATE TRIGGER (nome_trigger) (AFTER ou BEFORE) (ação INSERT, UPDATE ou DELETE)
ON (nome_tabela)FOR EACH ROW EXECUTE PROCEDURE (nome_da_function())

Quando um gatilho esta marcado FOR EACH ROW, o mesmo é chamado uma vez para cada linha que a operação modificar. Por exemplo: imagine que você precise inserir dez registros em uma tabela. Isso vai fazer com que todos os gatilhos ON INSERT sejam chamados dez vezes, uma vez para cada linha.

Se por ventura existir vários gatilhos do mesmo tipo, para o mesmo evento, eles serão disparados em ordem alfabética.

Os gatilhos são muito usados para tratamento de dados ou para efetuar alguma operação em tabelas, por exemplo: imagine que você tenha um sistema, que em uma determinada rotina, para deletar um registro, seja preciso gravar o mesmo em uma tabela de histórico.
Para resolver este problema, basta criar uma função para gravar este registro na tabela de histórico, e criar um gatilho para que sempre que o evento DELETE ocorra nesta tabela, o registro seja gravado no histórico.
Ou por exemplo, você precisa fazer uma validação em certa coluna da tabela para ver se o dado é um integer ou uma string para evitar que o banco gere uma mensagem de erro durante o processo. Para este caso, crie uma função que verifica se o dado é integer ou string e depois criar uma trigger chamando esta função no evento BEFORE INSERT.
Um gatilho é uma espécie de procedimento armazenado (stored procedures), mas não o chamamos como exemplo “SELECT minha_funcao ()”, mas sim sempre que um evento de insert, update e delete ocorrer na tabela.
Como vamos ver no próximo tópico, o que vai distinguir uma function de uma stored procedure, vai ser o tipo de dado que ela vai retornar que especificamos no momento que criamos a function, que no caso vai ser uma trigger.

FUNCTION
O comando CREATE FUNCTION cria uma função e o comando CREATE OR REPLACE FUNCTION, cria ou substitui uma função já existente.
Sempre que uma função é removida e recriada, essa função não será mais a mesma entidade que era antes. Nesse caso, vai ser necessário remover as regras, visões e gatilhos que fazem referência à função antiga. Sempre que por algum motivo, precisarmos modificar uma função, é aconselhável utilizar o comando CREATE OR REPLACE FUNCTION, pois, com isso, apenas a definição da função é modificada, sem invalidar os objetos que fazem referência à mesma.

Devido à grande flexibilidade do Postgresql, ele permite escrever funções em SQL, C ou outra linguagem procedural definida pelo usuário.
Agora vamos imaginar que antes de inserir na tabela principal do exemplo tb_main, temos que checar se o código de barras (codbarras) tem exatas 30 posições e que o número do lote (codtblote) não esta NULL.
Para fazer isto, vou criar uma função que vai fazer esta verificação, e se houver inconsistência nos dados, ela vai retornar com uma mensagem e cancelar a gravação de uma nova linha na tabela “tb_main”, como pode ser visto na Listagem 5.

Listagem 5. Função para verificar código de barras e lote
CREATE OR REPLACE FUNCTION ftb_main() RETURNS trigger
AS
$$
BEGIN
IF length(NEW.codbarras) < 30 THEN
RAISE EXCEPTION ‘CAMPO DEVE TER 30 POSIÇÕES!!’;
END IF;
IF NEW.codtblote IS NULL THEN
RAISE EXCEPTION ‘O LOTE ESTA VAZIO!!’;
END IF;
END;
$$
LANGUAGE ‘plpgsql’;

– para chamar esta função vou criar um gatilho

CREATE TRIGGER tg_tbmain BEFORE INSERT ON tb_main
FOR EACH ROW EXECUTE PROCEDURE ftb_main();

As funções também podem ser usadas para tratamento de exceções, por exemplo: imagine que por algum motivo, não podemos usar a primeira regra, definida na Listagem 3. Então vamos lembrar que no início do artigo criamos uma unique (Listagem 2) que define o código do produto (codtbprod) e o código de barras (codbarras) como único.
A partir desta unique, vou criar uma função que vai fazer um processo similar ao que nossa primeira regra fazia. A nova função vai “tentar” inserir uma nova linha na tabela principal, mas se essa nova linha tiver o mesmo código de produto e código de barras, o Postgresql vai gerar uma exceção de unique e vai interromper a ação.
Com o tratamento de erros vou conseguir “pegar” este erro gerado pelo Postgresql e gerar uma ação do tipo INSTEAD similar ao da regra, de acordo com a Listagem 6.

Listagem 6. Tratamento de exceção usando uma função
CREATE OR REPLACE FUNCTION func_tbmain(integer,integer,integer,integer,varchar(30)) RETURNS text
AS
$$
DECLARE
codlote ALIAS FOR $1;
codprod ALIAS FOR $2;
codorig ALIAS FOR $3;
coddstn ALIAS FOR $4;
codbras ALIAS FOR $5;
BEGIN
INSERT INTO tb_main (codtbmain,codtblote,codtbprod,codorigem,
coddestno,codbarras,dtatbmain)
VALUES ((SELECTSEXTVAL(’sequencial’)),codlote,codprod,
codorig,coddstn,codbras,(SELECT CURRENT_DATE));

INSERT INTO tb_hist (codtbhist,codtblote,codtbprod,codorigem,
coddestno,codbarras,dtatbhist,hratbhist)
VALUES ((SELECT NEXTVAL(’seqtb000′)),codlote,codprod,
codorig,coddstn,codbras,(SELECT CURRENT_DATE),(SELECT CURRENT_TIME));

RETURN codbras;

EXCEPTION
WHEN unique_violation THEN
INSERT INTO tb_hist (codtbhist,codtblote,codtbprod,codorigem,
coddestno,codbarras,dtatbhist,hratbhist)
VALUES ((SELECT NEXTVAL(’sequencial’)),codlote,codprod,
codorig,coddstn,codbras,(SELECT CURRENT_DATE),(SELECT CURRENT_TIME));

UPDATE tb_main SET codtblote = codlote WHERE codtbprod = codprod AND codbarras =    codbras;
RETURN codbras;
END;
$$
LANGUAGE ‘plpgsql’;

– para chamar esta função, vou usar o SELECT nome(passando os parametros)

SELECT func_tbmain(parametro1,parametro2,parametro3…)

Se durante o processo de inclusão não ocorrer nenhum erro de unique_violation, a função vai simplesmente gravar uma nova linha na tabela tb_main e depois na tb_hist e vai passar direto para a instrução END, ignorando assim tudo que vem depois do EXCEPTION da função.
Mas se ocorrer exceção, o primeiro bloco da função é ignorado e o controle passa para a lista de EXCEPTION, executando todo o bloco até o fim.

Se dentro de um bloco de comando de uma função, não existir o EXCEPTION e um erro fosse capturado, a função seria interrompida.
Com o EXCEPTION na função, esse mesmo erro seria capturado, o bloco de comandos acima do EXCEPTION seria ignorado e o bloco de comando logo abaixo do EXCEPTION seria executado, evitando assim que a função fosse interrompida.

CONCLUSÃO
Neste artigo vimos uma pequena introdução para regras, funções e gatilhos no Postgresql. Claro que há muito mais coisas para serem vistas a esse respeito, mas fica o registro para as pessoas que iniciam nesta área, e também para os que querem migrar ou desenvolver aplicações.

Links
Documentação do Postgresql
http://pgdocptbr.sourceforge.net/pg82/index.html
Tutorial
http://pgdocptbr.sourceforge.net/pg82/tutorial.html
Referencias
http://pgdocptbr.sourceforge.net/pg82/reference.html

Rodrigo Ibraim

PGOpen Consultoria