Criando domínio com função de validação no PostgreSQL

Publicado por Roberto Rezende de Assis em 06/05/2006

[ Hits: 15.864 ]

0 0

Denuncie Favoritos Indicar Impressora

Criando domínio com função de validação no PostgreSQL

Bem, o que vou escrever aqui é uma tradução/adaptação do que eu coloquei na seção de comentários da documentação do PostgreSQL 8.1:

http://www.postgresql.org/docs/8.1/interactive/sql-createdomain.html

Suponhamos que você criou uma base de dados, mas não criará os programas que interfacearão com ela. Assim, para garantir a integridade dos seus dados, é melhor tomar algumas precauções.

O uso de domínios é uma precaução bastante interessante no caso de haver colunas em tabelas que possuam regras de aceitação específicas, o que é o caso do CPF. Há uma regra para definir se ele é válido ou não.

Dessa maneira, vamos fazer um exemplo simples e executá-lo.

Nós temos um número de documento que chamaremos de doc, que é composto de 3 números. Os dois primeiros são os números do documento propriamente dito, o terceiro é o resto da divisão por 2 dos dois primeiros. Ficamos, então, com números assim:

100 É válido
101 Não é válido
111 É válido
110 Não é válido

Vamos criar um arquivo (exemplo: domain.sql) e começar a programar. Primeiro a função de validação que deverá retornar 'bool':

CREATE OR REPLACE FUNCTION validar_doc(char(3))
RETURNS bool AS $$
DECLARE
doc ALIAS FOR $1;
numero int;
digito_verificacao int;
BEGIN
IF char_length(doc) < 3 THEN
RAISE EXCEPTION 'O DOC deve possuir 3 digitos';
END IF;
numero := to_number(substr(doc,1,2),'99');
digito_verificacao := to_number(substr(doc,3,1),'9');
IF mod(numero,2) = digito_verificacao THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;

Agora o domínio:

CREATE DOMAIN doc AS char(3) CHECK (validar_doc(VALUE) = TRUE);

A tabela:

CREATE TABLE documento (numero doc);

As inserções:

INSERT INTO documento VALUES ('100'); -- ok
INSERT INTO documento VALUES ('020'); -- ok
INSERT INTO documento VALUES ('071'); -- ok
INSERT INTO documento VALUES ('101'); -- inválido
INSERT INTO documento VALUES ('202'); -- inválido
INSERT INTO documento VALUES ('20'); -- pequeno demais
INSERT INTO documento VALUES ('2030'); -- grande demais

Uma consulta para ver o que nós temos:

mysql> SELECT * FROM document;

Ao executar o script a saída esperada será essa:

CREATE FUNCTION
CREATE DOMAIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:f:/pgsql/domain.sql:34: ERROR: value for domain doc violates check constraint "doc_check"
psql:f:/pgsql/domain.sql:35: ERROR: value for domain doc violates check constraint "doc_check"
psql:f:/pgsql/domain.sql:36: ERROR: O DOC deve possuir 3 digitos
psql:f:/pgsql/domain.sql:37: ERROR: value too long for type character(3)
numero
--------
100
020
071
(3 rows)

Dessa maneira, o conteúdo da tabela está protegido contra a inserção de valores DOC que sejam inválidos.

Observação: no manual há exatamente isso aqui:

"CHECK (expression)

CHECK clauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the name VALUE to refer to the value being tested.

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than VALUE. "

Outras dicas deste autor

Nenhuma dica encontrada.

Leitura recomendada

PostgreSQL: Fazendo backup de um servidor inteiro

Fazendo backup com PostgreSQL

Acessando dois databases no mesmo SELECT (PostgreSQL)

Evitando erros ao indexar campos timestamp em SQL

Consulta aleatória com PostgreSQL

Comentários

Nenhum comentário foi encontrado.