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

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

[ Hits: 14.492 ]

 


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:
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

Lidando com nulos (NULL) em SQL

Modelagem de bancos de dados

Autovacuum do PostgreSQL

Fazendo backup com PostgreSQL

PostgreSQL: Fazendo backup de um servidor inteiro

  

Comentários

Nenhum comentário foi encontrado.



Contribuir com comentário




Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner
Linux banner
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts