Funções - PostgreSQL

Nesse artigo quero abordar um pouco sobre as funções em bancos de dados, o que elas são, como podem ser úteis e como implementá-las em específico no PostgreSQL.

Funções SQL

Primeiramente, as functions são um conceito bem comum e presente em grande parte das linguagens de programação (pra não dizer TODAS). Resumindo, as funções são um conjunto de comandos que executam uma ação específica, podendo retornar um resultado ou não.

As funções são bem úteis quando ser precisa executar tarefas repetitivas e principalmente, para abstrair tarefas complexas.

Em SGBDs como PostgreSQL, MySQL e SQL Server temos dois "tipos" de funções, as internas como as NOW() , MIN() e MAX() com propósitos mais genéricos. Também temos as funções definidas pelo usuário, chamadas de User Defined Functions que são implementadas pelo próprio usuário para um propósito mais específico dentro da sua base de dados.

Caso de Uso

Uma situação bem comum já me deparei e que as funções definidas pelo usuários vieram a ser úteis foi quando precisei validar números de CPF que já estavam persistidos na base e obter aqueles que seriam inválidos.

É interessante que os dados sejam validados antes da inserção, entretanto, o estrago estava feito e era preciso agir kkkkkk.

A validação do CPF é feita a partir de um algoritmo simples, muito bem descrito no seguinte artigo.

Simplificando as duas etapas feitas no algoritmo de validação, primeiramente é necessário multiplicar os 9 primeiros dígitos por um sequência de números de 10 à 2, somando esse resultado. Ao dividir esse resultado por 11, consideramos o resto da divisão e comparamos com o penúltimo dígito. Caso esta condição for verdadeira, o CPF já será invalido, caso não, seguimos para a próxima validação.

A função internaMOD() efetua a divisão entre dois números, retornando o resto dessa divisão.

IF (MOD(total, 11) < 2 AND verifying_digit_one != 0) OR
   (MOD(total, 11) > 2 AND verifying_digit_one != 11 - (MOD(total, 11))) THEN
    RETURN FALSE;
END IF;

A segunda etapa é bem parecida com a anterior, porém, a multiplicação é realizada com os 10 primeiros dígitos pela sequência de 11 à 2, somando o resultado e obtendo o resto da divisão por 11, sendo utilizado para seguinte validação, se a condição for verdadeira o CPF será invalido.

IF (MOD(total, 11) < 2 AND verifying_digit_two != 0) OR
   ((MOD(total, 11) > 2 AND verifying_digit_two != 11 - (MOD(total, 11)))) THEN
        RETURN FALSE;
END IF;

Caso a função realize ambas validações e nenhuma das condições seja verdadeira, o cpf analisado será válido. Agora que temos as duas validações, seguimos para a criação da função dentro do banco de dados.

Implementação

Primeiro, especificamos o comando de criação e atualização (Caso essa função já exista), o nome da função que será isCpfValid, como parâmetro a função receberá o número de cpf, e o retorno será TRUE OU FALSE. Além disso, já declaramos todas as variáveis que serão necessárias com o bloco DECLAREe no fim especificamos a linguagem que utilizaremos na função.

CREATE OR REPLACE FUNCTION isCpfValid(cpf VARCHAR(14)) RETURNS bool as
$$
DECLARE
    cleaned_cpf         VARCHAR(14);
    verifying_digit_one INTEGER;
    verifying_digit_two INTEGER;
    total               INTEGER;
    counter             INTEGER;
    multiplier          INTEGER;
BEGIN
 -- A função será escrita neste bloco
END;
$$ LANGUAGE plpgsql;

Com essa estrutura podemos codificar o algoritmo de validação que foi abordado anteriormente.

Um ponto importante é a limpeza do número informado, que pode conter ou não pontos e traços, algo que irá prejudicar a validação, para isso fazemos:

cleaned_cpf := REPLACE(cpf, '.', '');
cleaned_cpf := REPLACE(cleaned_cpf, '-', '');

Após a limpeza, podemos realizar uma primeira validação, bem simples, que tem como objetivo validar números compostos apenas por um dígito. Caso o número possua apenas dígitos repetidos ele será considerado um número inválido, por isso a seguinte validação:

IF cleaned_cpf IN
       ('11111111111', '22222222222', 
        '33333333333', '44444444444',
        '55555555555', '66666666666', 
        '77777777777', '88888888888',
        '99999999999')
    THEN
        RETURN FALSE;
    END IF;

Antes de iniciar as duas etapas para a validação, é necessário obter os dois dígitos verificadores. Essa tarefa é simples com o uso da função SUBSTRING(), que retorna partes especificadas de uma string original.

verifying_digit_one := SUBSTRING(cleaned_cpf FROM 10 FOR 1);
verifying_digit_two := SUBSTRING(cleaned_cpf FROM 11 FOR 1);

Em seguida, partimos para a multiplicação e soma dos dígitos do cpf pela sequência de 10 à 2, que será realizada pela execução de um loop WHILE. Com o resultado computado, efetuamos a primeira validação.

total := 0;
counter := 1;
multiplier := 10;

WHILE counter < 10
LOOP
    total := total + SUBSTRING(cleaned_cpf, counter, 1)::integer * multiplier;
    multiplier := multiplier - 1;
    counter := counter + 1;
END LOOP;

IF
  (MOD(total, 11) < 2 AND verifying_digit_one != 0) OR
  (MOD(total, 11) > 2 AND verifying_digit_one != 11 - (MOD(total, 11))) THEN
   RETURN FALSE;
end if;

Na segunda validação, realizamos um loop semelhante ao utilizado anteriormente, mas iterando a sequência de 11 à 2 e ao final, realizando a validação.

total := 0;
counter := 1;
multiplier := 11;

WHILE counter < 11
  LOOP
  total := total + substring(cleaned_cpf, counter, 1)::integer * multiplier;
            multiplier := multiplier - 1;
            counter := counter + 1;
        END LOOP;

    IF (MOD(total, 11) < 2 AND verifying_digit_two != 0) OR
       ((MOD(total, 11) > 2 AND verifying_digit_two != 11 - (MOD(total, 11)))) THEN
        RETURN FALSE;
    end if;

E por fim, caso o cpf que está sendo analisado não satisfaça nenhuma das condições acima, temos então um CPF totalmente válido, por isso retornamos o TRUE.

RETURN TRUE;

Ao final, teremos um bloco de código enorme que será abstraído para uma simples função.

CREATE OR REPLACE FUNCTION isCpfValid(cpf VARCHAR(14)) RETURNS bool as
$$
DECLARE
    cleaned_cpf         VARCHAR(14);
    verifying_digit_one INTEGER;
    verifying_digit_two INTEGER;
    total               INTEGER;
    counter             INTEGER;
    multiplier          INTEGER;
BEGIN
    cleaned_cpf := REPLACE(cpf, '.', '');
    cleaned_cpf := REPLACE(cleaned_cpf, '-', '');

    IF cleaned_cpf IN
       ('11111111111', '22222222222', 
        '33333333333', '44444444444',
        '55555555555', '66666666666',
        '77777777777', '88888888888',
        '99999999999')
    THEN
        RETURN FALSE;
    END IF;

    verifying_digit_one := SUBSTRING(cleaned_cpf FROM 10 FOR 1);
    verifying_digit_two := SUBSTRING(cleaned_cpf FROM 11 FOR 1);

    total := 0;
    counter := 1;
    multiplier := 10;

    WHILE counter < 10
        LOOP
            total := total + SUBSTRING(cleaned_cpf, counter, 1)::integer * multiplier;
            multiplier := multiplier - 1;
            counter := counter + 1;
        END LOOP;

    IF
            (MOD(total, 11) < 2 AND verifying_digit_one != 0) OR
            (MOD(total, 11) > 2 AND verifying_digit_one != 11 - (MOD(total, 11))) THEN
        RETURN FALSE;
    end if;

    total := 0;
    counter := 1;
    multiplier := 11;

    WHILE counter < 11
        LOOP
            total := total + substring(cleaned_cpf, counter, 1)::integer * multiplier;
            multiplier := multiplier - 1;
            counter := counter + 1;
        END LOOP;

    IF (MOD(total, 11) < 2 AND verifying_digit_two != 0) OR
       ((MOD(total, 11) > 2 AND verifying_digit_two != 11 - (MOD(total, 11)))) THEN
        RETURN FALSE;
    end if;

    RETURN TRUE;
end;
$$ LANGUAGE plpgsql;

Agora com a função implementada e criada podemos executar a validação de uma forma mais simples, ficando disponível à todos os clientes da base de dados. Que poderá ser executada em qualquer tabela que possua algum campo de cpf.

SELECT name, email, isCpfValid(cpf) AS isValid FROM users;
-- OU 
SELECT isCpfValid('767.682.810-46')

Isso é tudo pessoal!

Esse artigo foi apenas uma breve e simples explicação sobre funções, um dos diversos recursos poderosos que os bancos de dados possuem. Espero que com esse artigo seja possível entender como as funções funcionam e em quais cenários que elas poderão ser utilizadas dado o exemplo abordado. E se você chegou até aqui, agradeço pela leitura e atenção!