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

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

[ Hits: 15.840 ]

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

Documentação do PosgreSQL 8.0 em Português_BR

Acesso externo no PostgreSQL 8.4

PostgreSQL - Bancos com diferentes encodings

Guardando e Recuperando Dados JSON em Tabela PostgreSQL

PostgreSQL: Concedendo ou revogando privilégios em colunas

Comentários

Nenhum comentário foi encontrado.