DEV Community: Alexandre Lopes

Funções - PostgreSQL

Alexandre Lopes — Sun, 23 May 2021 13:36:53 +0000

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!

Restauração de BD - PostgreSQL + Docker

Alexandre Lopes — Sun, 30 Aug 2020 18:00:17 +0000

Nesse artigo farei uma abordagem sobre uma forma que encontrei para restaurar uma base de dados PostgreSQL em um container Docker.

Atualmente estou estagiando e para facilitar a configuração do meu ambiente de desenvolvimento, padronizando com o do restante da equipe da empresa, além de desenvolver meu conhecimento com containers (comecei os estudos recentemente e quero praticar), resolvi trabalhar com a base de dados em um container Docker.

Preparando o ambiente

Primeiro, criei o arquivo docker-compose.yml para facilitar a definição e a execução dos contêineres.

version: '3'
services: 
  postgres:
    build: .
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports: 
      - "5432:5432"
    volumes:
      - ./pg-data:/var/lib/postgresql/data
      - ./backups:/var/lib/postgresql/backups
    networks:
      - postgres-network

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: "seu_email@email.com"
      PGADMIN_DEFAULT_PASSWORD: "admin!"
    ports:
      - "16543:80"
    depends_on:
      - postgres
    networks:
      - postgres-network

networks: 
  postgres-network:
    driver: bridge

No meu caso, tive alguns problemas com a codificação do SGBD e para resolvê-lo, tive que especificar para o container PostgreSQL a linguagem e o encoding que seriam utilizados.

FROM postgres:12

RUN localedef -i pt_BR -c -f UTF-8 -A /usr/share/locale/locale.alias pt_BR.utf-8
ENV LANG pt_BR.UTF-8

Esses arquivos entregam uma configuração bem simples para o banco de dados PostgreSQL e para o PG Admin, entretanto, o que precisa-se ser destacado são os dois volumes usados pelo PostgreSQL, sendo eles:

pg-data: volume dedicado para a persistência dos dados geridos pelo BD, mesmo que os serviços sejam reiniciados.
backups: volume dedicado para o armazenamento dos arquivos que serão usados durante a restauração.

Para iniciar os serviços necessários, dentro do diretório do arquivo docker-compose.yml execute o comando:

docker-compose up -d

Executando a restauração

Com o ambiente criado e executando, para dar início à restauração é necessário deixar os arquivos de restauração disponíveis para o container do PostgreSQL, para isso, copie o arquivo que será usado na restauração de dados para a pasta /backup.

Agora podemos acessar o container de forma interativa, para executarmos os comandos para a restauração.

docker exec -it <nome_container> /bin/bash

Já dentro do container do PostgreSQL, acesse a pasta localizada em /var/lib/postgresql/, nesse diretório estarão disponíveis os backups.

cd /var/lib/postgresql/

A restauração do diretório acontecerá em duas etapas, agora dentro do diretório específico do PostgreSQL, podemos iniciar a restauração com o seguinte comando.

Restaurando o schema

pg_restore 
  --no-owner -U postgres 
  --dbname= --create 
  --verbose -c 
  --schema-only backups/<nome_arquivo>.pgdump

Esse comando com várias flags, que são explicadas detalhadamente neste link, basicamente criará um novo banco de dados e em seguida, fará a importação apenas do schema, deixando os dados para o próximo passo.

Restaurando os dados

Agora que já temos todo a estrutura do banco de dados, precisamos da parte principal, os dados. Usaremos o mesmo comando para importar os dados, entretanto com algumas opções diferentes.

pg_restore 
  --no-owner -U postgres 
  --dbname=<nome_bd> 
  --verbose --data-only 
  --superuser=postgres
  --disable-triggers backups/<nome_arquivo>.pgdump

Com esse comando apenas dados serão importados para dentro do bd que foi especificado no comando. Um ponto que vale ser destacado trata-se da opção --disable-triggers, que desabilitará a checagem de integridade dos registros, algo que não é muito importante na restauração de um banco de dados, já que esses dados são provindos de outra base, que provavelmente já verificou a integridade desses dados, o que nos livra de vários erros, pois durante o processo, pode-se haver a referência de dados que ainda não foram restaurados.

Conclusão

Pronto, seguindo esses passos você terá restaurado uma base de dados no PostgreSQL rodando com em um container. Essa pode não ser a abordagem perfeita, levando em consideração minha inexperiência utilizando Docker, porém funcionou muito bem para o meu cenário e pode ajudar outras pessoas. Destaco também que esse ambiente é específico para o desenvolvimento. Caso seja preciso, você pode encontrar o repositório completo desse tutorial aqui.

P.S.: Esse é o meu primeiro artigo por aqui, gostei bastante de plataforma e estou aberto para sugestões e dicas, tanto na escrita quanto nas tecnologias presentes nesse artigo.