Postgresql – Funções

Neste post, veremos as funções (FUNCTIONS ou PROCEDURES), um recurso muito importante quando estamos falando de bancos de dados.

Cada banco de dados implementa funções de uma forma um pouco diferente uns dos outros. Para os exemplos aqui listados, veremos como funciona as funções no SGBD PostgreSQL.

Funções (ou procedimentos) são trechos de código que ficam armazenados dentro do banco de dados.

Da mesma forma que existem funções prontas no banco de dados (exemplo: SUM, AVG, MAX…), podemos criar nossas próprias funções.

Para o Postgresql, o mesmo suporta muitos tipos de linguagem para a criação de funções dentro do banco de dados. Aqui veremos a linguagem PL/pgSQL, que é a mais utilizada.

A sintaxe para a criação de uma nova função ou procedimento é (de forma simplificada):

CREATE OR REPLACE FUNCTION nome_da_funcao ( parametros )
    RETURNS tipo_retorno AS
$$
DECLARE
      --declaracao de variaveis
BEGIN
      --conteudo da funcao
END;
$$ LANGUAGE linguagem;

Exemplo prático:

Temos a seguinte tabela no banco de dados:


CREATE TABLE usuario (
id integer NOT NULL,
nm_login character varying,
ds_senha character varying,
fg_bloqueado boolean,
nu_tentativa_login integer,
CONSTRAINT pk_usuario PRIMARY KEY (id)
);

Na tabela ‘usuario’, temos os seguintes dados:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 0
2 joao 123456 false 0
3 maria abcd1234 false 2

Um exemplo básico de uma função seria criar uma função onde passamos o login do usuário e queremos que a função retorne o seu ID.

Apesar de ser um exemplo sem muito sentido, servirá de base para o próximo exemplo.

Podemos criar, então, a função da seguinte forma:


CREATE OR REPLACE FUNCTION get_id ( varchar ) RETURNS integer AS
$$
DECLARE
        variavel_id INTEGER;
BEGIN
        SELECT INTO variavel_id id FROM usuario WHERE nm_login = $1;
        RETURN variavel_id;
END;
$$ LANGUAGE 'plpgsql';

A função acima (chamada get_id) recebe como parâmetro um VARCHAR e retorna um INTEGER, e funciona da seguinte forma:

– declara uma variável chamada variavel_id, do tipo INTEGER;

faz um select na tabelas de usuário onde a coluna nm_login é igual ao varchar recebido como parâmetro, e colocar o valor de id encontrado dentro da variável variavel_id (através do comando SELECT INTO);

– retorna a variavel_id.

O $1 representa o primeiro parâmetro recebido, o $2 o segundo, e assim por diante.

Agora, se executarmos a seguinte instrução SQL:

SELECT get_id( 'joao' ); 

teremos o seguinte resultset:

get_id
2

Um exemplo mais complexo

Vamos supor que queremos agora criar uma regra que, se o usuário entrar com a senha errada três vezes seguidas, ele deve ser bloqueado.

A coluna ‘fg_bloqueado’ mostra se o usuário está bloqueado ou não, e a coluna ‘nu_tentativa_login’ mostra quantas vezes seguidas este usuário inseriu sua senha errada.

Exemplo: se um usuário que tem o número de tentativas (coluna nu_tentativa_login) = 1 entrar com sua senha errada, o numero de tentativas deverá ser alterado para 2. Caso entre novamente com a senha errada, o número de tentativas deverá ser alterado para 3 E a coluna fg_bloqueado deverá ser alterada para TRUE.

Caso um usuário com o número de tentativas = 2 tenha entrado com a senha correta, o valor do número de tentativas deverá voltar a ser 0.

Toda esta lógica pode ser implementada diretamente no banco de dados, através de uma função.

SIM!

Vejamos a seguinte função:


CREATE OR REPLACE FUNCTION set_tentativa_login ( VARCHAR, VARCHAR )
    RETURNS VOID AS
$$
DECLARE
        registro RECORD;
        tentativas INTEGER;
BEGIN
        SELECT INTO registro id, fg_bloqueado, nu_tentativa_login FROM usuario WHERE nm_login = $1 AND ds_senha = $2;
        IF registro IS NULL
        THEN
            SELECT INTO tentativas nu_tentativa_login FROM usuario WHERE nm_login = $1;
            tentativas := tentativas + 1;
            IF tentativas > 2
            THEN
                UPDATE usuario SET nu_tentativa_login = tentativas, fg_bloqueado = TRUE where nm_login = $1;
            ELSE
                UPDATE usuario SET nu_tentativa_login = tentativas where nm_login = $1;
            END IF;
        ELSE
            UPDATE usuario SET nu_tentativa_login = 0 where nm_login = $1;
        END IF;
END;
$$
LANGUAGE 'plpgsql';

SIM!!!!!

É possível realizar estruturas de decisão e de repetição em funções de bancos de dados.

Explicando um pouco:

A função set_tentativa_login recebe dois parâmetros: um sendo o login e o outro a senha do usuário. Primeiro é verificado se existe um usuário com o login e senha informado. O resultado é colocado dentro de uma variável do tipo RECORD, que representa um registro (com várias colunas) de uma tabela.

Caso exista o usuário, seu número de tentativas é alterado para ZERO. Caso não exista, é efetuada outra consulta, desta vez somente através de seu login. O número de tentativas é armazenado na variável tentativas, e este valor é incrementado em 1. Além de atualizar o número de tentativas, é verificado se este valor é maior que 2. Caso seja, altera o valor da coluna fg_bloqueado para TRUE.

Na prática:

Utilizamos então a seguinte instrução:

SELECT set_tentativa_login( 'hallan', 'senha_errada' );

A tabela de usuários ficará da seguinte forma:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 1
2 joao 123456 false 0
3 maria abcd1234 false 2

Como foi passada a senha errada, o número de tentativas foi incrementado em 1.

Caso seja executado a SQL:

SELECT set_tentativa_login( 'maria', 'senha_errada' );

A tabela de usuários ficará da seguinte forma:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 1
2 joao 123456 false 0
3 maria abcd1234 true 3

Além da senha, o usuário maria teve sua coluna fg_bloqueado alterado para TRUE.

Por fim, se executarmos:

SELECT set_tentativa_login( 'hallan', 'hallan2011' );

A tabela de usuários ficará da seguinte forma:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 0
2 joao 123456 false 0
3 maria abcd1234 true 3

Teremos o valor da coluna nu_tentativa_login alterado para ZERO, pois desta vez o valor do campo senha estava correto.

 

É possível resolver muitas operações complexas através de funções de bancos de dados.

Até a próxima!

Deixe um Comentário

3 Comentários.

  1. Obrigada! As informações me ajudaram muito mesmo!

  2. muito obrigado hallan.

  3. 😮 😀 Muito bom, ajudou a entender, além de está resumido, obrigado!

Deixe um Comentário


NOTA - Você pode usar estesHTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>