Linux / PHP / Java / Designer

As melhores dicas estão aqui.

Criando funções Loop no PostgreSQL

| 0 comentários

Criar funções no PostgreSQL, que são muito úteis para diversos recursos quando trabalhamos com uma grande quantidade de dados.

Estrutura da Função em PostgreSQL

CREATE FUNCTION NOME_DA_FUNCAO (param1, param2) RETURNS TIPO_RETORNO AS $$
$$ LANGUAGE LINGUAGEM_USADA;
­­CORPO DA FUNÇÃO

Temos a estrutura básica de uma função, vamos aos detalhes:

  1. NOME_DA_FUNCAO: Como o próprio nome já sugere aqui você deve colocar o nome da sua função, sem acentos, espaços ou caracteres especiais.
  2. param1, param2: A sua função pode receber parâmetros de diversos tipos e nomes, aqui mostramos apenas dois, mas você pode usar mais que dois parâmetros ou nenhum.
  3. TIPO_RETORNO: Pode-se retornar um valor (inteiro, varchar, boolean e etc) ou não retornar nada (void).
  4. $$: Esse caractere tanto no início como no fim da função, serve para dizer onde começa e onde termina respectivamente.
  5. LINGUAGEM_USADA: O PostgreSQL suporta diversos tipos de linguagens para você desenvolver suas funções, nesse usaremos plpgsql, mas fique sabendo que você pode desenvolver até em “C”.

Nada melhor do que exemplos práticos para aprender como funciona. Primeiro vamos criar uma tabela que nos servirá de exemplo. Observe o código:

Tabela pessoa_fisica

 CREATE TABLE pessoa_fisica (
      id_pessoa SERIAL,
      nome VARCHAR(80),
      sobrenome VARCHAR(200),
      sexo CHAR(1),
      cpf CHAR(11),
      PRIMARY KEY(id_pessoa)
  );

Antes de continuar é importante que haja alguns dados na tabela, então insira alguns e continue.

Vamos começar com uma função simples que apenas retorna os dados da tabela pessoa_fisica.

CREATE FUNCTION get_pessoas() RETURNS SETOF pessoa_fisica AS $$
BEGIN
RETURN QUERY SELECT * FROM pessoa_fisica
RETURN;
END;
$$ LANGUAGE 'plpgsql'

O nome da função acima é “get_pessoas()” que retorna um SETOF (uma lista de registros) do tipo pessoa_fisica. Poderíamos também usar o RECORD

Qual a diferença entre o retorno do tipo pessoa_fisica e o RECORD? O retorno do tipo pessoa_fisica, como o próprio nome já sugere, retorna registros da tabela pessoa_fisica e não nos da a opção de retornar só alguns campos ou mesmo fazer um JOIN com outras tabelas retornando campos de ambos, para isso usamos o RECORD. O RECORD permite que você faça retornar apenas algumas colunas do seu interesse.

Vamos ver agora como usar o FOR LOOP em funções no PostgreSQL.

CREATE OR REPLACE FUNCTION meuesquema.telefones()
    RETURNS SETOF meuesquema.telefone AS
  $BODY$
     DECLARE
        --cursor
        reg meuesquema.telefone%ROWTYPE;
  BEGIN
           --realiza um loop em todos os telefones da tabela
        FOR reg in
                   SELECT tel.numero, tel.ddd, tel.operadora
                   FROM meuesquema.telefone tel
           LOOP
              RETURN NEXT reg;
        END LOOP;
        RETURN;
     END;
     $BODY$
    LANGUAGE plpgsql VOLATILE;

Vamos apontar alguns pontos importantes na listagem acima:

  1. %rowtype: A palavra-chave rowtype junta do símbolo %, diz que a variável reg irá armazenar um registro do tipo “meuesquema.telefone”, ou seja, uma linha da tabela telefone no esquema “meuesquema”. Poderíamos também usar o RECORD, substituindo toda a declaração por apenas RECORD, mas não precisamos disso no momento.
  2. FOR: Realizamos um SELECT que retorna o numero, ddd e operadora a partir da tabela telefone no esquema meuesquema. A cada iteração no laço FOR, o registro é armazenado na variável reg que pode ser manipulada dentro da nossa função. Podíamos, por exemplo, chamar o campo numero com: “reg.numero”.
  3. LOOP e END LOOP: Estes são os delimitadores do laço, que dizem onde começa e onde termina o LOOP.
  4. $BODY$: Este delimita o início e o fim do “corpo” da função, ou seja, onde a lógica realmente está implementada.

Podemos também optar pela criação de funções que não retornam nada, ou seja, void. Observe.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;

Na função acima temos uma exemplo da atualização da tabelas users sem nenhum retorno, ou seja, ao executar a função nada será retornado, apenas executado internamente. Perceba que temos um variável chamada “curtime” que recebe como valor padrão a data e hora atual em que a função está sendo executada. Como parâmetro da função temos o id e o comentário que será atualizado para a tabela usuários.

 

Você pode utilizar o RAISE NOTICE quando desejar mostrar mensagens (depuração) enquanto estiver rodando a função, em casos onde a função irá demorar muito tempo para finalizar o RAISE NOTICE pode ajudar a mostrar o progresso da função, por exemplo.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
RAISE NOTICE 'Atualizando registro id = %',id;
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;

Perceba também que conforme mostramos novos recursos aprendemos outros importantes. O conjunto IF THEN e END IF delimitam um bloco condicional, ou seja, só entrará neste bloco se a condição proposta pelo IF THEN for verdadeira. Aproveitando que estamos falando de bloco condicional, vamos ver como ficaria se desejamos acrescentar o SENÃO.

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $
      DECLARE
          curtime timestamp := now();
      BEGIN
         IF id = 3 THEN
           RAISE EXCEPTION 'Ops, o usuario de numero 3 não pode ser atualizado';
         ELSE
                UPDATE users SET last_modified = curtime, comment = comment
            WHERE users.id = id;
         END IF;   
      END;
 $$ LANGUAGE plpgsql;

 

Deixe uma resposta

Campos requeridos estão marcados *.