PGSQL

As funções em linguagens procedurais no PostgreSQL, como a Pl/pgSQL são correspondentes ao que se chama comumente de Stored Procedures (procedimento armazenado).

A Pl/pgSQL é a linguagem de procedimentos armazenados mais utilizada no PostgreSQL, devido ser a mais madura e com mais recursos.

Vantagens da utilização da linguagem PL/pgSQL

A linguagem SQL é a que o PostgreSQL (e a maioria dos bancos de dados relacionais) utiliza como linguagem de comandos. É portável e fácil de ser aprendida. Entretanto, todas as declarações SQL devem ser executadas individualmente pelo servidor de banco de dados.

Isto significa que o aplicativo cliente deve enviar o comando para o servidor de banco de dados, aguardar que seja processado, receber os resultados, realizar algum processamento, e enviar o próximo comando para o servidor. Tudo isto envolve comunicação entre processos e pode, também, envolver tráfego na rede se o cliente não estiver na mesma máquina onde se encontra o servidor de banco de dados.

Usando a linguagem PL/pgSQL pode ser agrupado um bloco de processamento e uma série de comandos dentro do servidor de banco de dados, juntando o poder da linguagem procedural com a facilidade de uso da linguagem SQL, e economizando muito tempo, porque não há necessidade da sobrecarga de comunicação entre o cliente e o servidor. Isto pode aumentar o desempenho consideravelmente.

Tipos de dado suportados nos argumentos e no resultado

As funções escritas em PL/pgSQL aceitam como argumento qualquer tipo de dado escalar ou matriz suportado pelo servidor, e podem retornar como resultado qualquer um destes tipos. As funções também aceitam e retornam qualquer tipo composto (tipo linha) especificado por nome. Também é possível declarar uma função PL/pgSQL como retornando record, significando que o resultado é um tipo linha, cujas colunas são determinadas pela especificação no comando que faz a chamada

Dicas para desenvolvimento em PL/pgSQL

Uma boa maneira de desenvolver em PL/pgSQL é utilizar o editor de texto preferido para criar as funções e, em outra janela, utilizar o psql para carregar e testar as funções desenvolvidas. Se estiver sendo feito desta maneira, é uma boa idéia escrever a função utilizando CREATE OR REPLACE FUNCTION. Fazendo assim, basta recarregar o arquivo para atualizar a definição da função.

Outra boa maneira de desenvolver em PL/pgSQL, é utilizar uma ferramenta de acesso ao banco de dados com interface gráfica que facilite o desenvolvimento em linguagem procedural. Um exemplo deste tipo de ferramenta é o PgAccess, mas existem outras. Estas ferramentas geralmente disponibilizam funcionalidades úteis como o escape de apóstrofos, e tornam mais fácil recriar e depurar funções.

Tratamento dos apóstrofos

O código da função PL/pgSQL é especificado no comando CREATE FUNCTION como um literal cadeia de caracteres. Se o literal cadeia de caracteres for escrito da maneira usual, que é entre apóstrofos ('), então os apóstrofos dentro do corpo da função devem ser duplicados; da mesma maneira, as contrabarras dentro do corpo da função (\) devem ser duplicadas. Duplicar os apóstrofos é no mínimo entediante, e nos casos mais complicados pode tornar o código difícil de ser compreendido, porque pode-se chegar facilmente a uma situação onde são necessários seis ou mais apóstrofos adjacentes. Por isso, recomenda-se que o corpo da função seja escrito em um literal cadeia de caracteres delimitado por "cifrão" em vez de delimitado por apóstrofos. Na abordagem delimitada por cifrão os apóstrofos nunca são duplicados e, em vez disso, toma-se o cuidado de escolher uma marca diferente para cada nível de aninhamento necessário.

Existem dois tipos de comentários no PL/pgSQL. O hífen duplo (--) começa um comentário que se estende até o final da linha. O /* começa um bloco de comentário que se estende até a próxima ocorrência de */. Os blocos de comentário não podem ser aninhados, mas comentários de hífen duplo podem estar contidos em blocos de comentário, e os hífens duplos escondem os delimitadores de bloco de comentário /* e */.

Declarações

Todas as variáveis utilizadas em um bloco devem ser declaradas na seção de declarações do bloco (A única exceção é a variável de laço do FOR interagindo sobre um intervalo de valores inteiros, que é automaticamente declarada como sendo do tipo inteiro).

As variáveis da linguagem PL/pgSQL podem possuir qualquer tipo de dado da linguagem SQL, como integer, varchar e char.

Parâmetros de Função

Os parâmetros passados para as funções recebem como nome os identificadores $1, $2, etc. Opcionalmente, para melhorar a legibilidade do código, podem ser declarados aliases para os nomes dos parâmetros $n. Para fazer referência ao valor do parâmetro, pode ser utilizado tanto o aliás quanto o identificador numérico.

Existem duas maneiras de criar um aliás. A forma preferida é fornecer nome ao parâmetro no comando CREATE FUNCTION

A outra maneira, que era a única disponível antes da versão 8.0 do PostgreSQL, é declarar explicitamente um aliás utilizando a sintaxe de declaração

nome ALIAS FOR $n;

O exemplo acima escrito utilizando este estilo fica da seguinte maneira:

CREATE FUNCTION taxa_de_venda(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Quando o tipo retornado por uma função PL/pgSQL é declarado como sendo de um tipo polimórfico (anyelement ou anyarray), é criado o parâmetro especial $0. Seu tipo de dado é o tipo de dado real a ser retornado pela função, conforme deduzido a partir dos tipos da entrada corrente. Isto permite à função descobrir o verdadeiro tipo de dado retornado para a entrada corrente. O parâmetro $0 é inicializado como nulo e pode ser modificado pela função, portanto pode ser utilizado para armazenar o valor a ser retornado se for desejado, embora não seja requerido. Também pode ser criado um aliás para o parâmetro $0. Por exemplo, esta função funciona com qualquer tipo de dado que possua o operador +:

CREATE FUNCTION somar_tres_valores(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    resultado ALIAS FOR $0;
BEGIN
    resultado := v1 + v2 + v3;
    RETURN resultado;
END;
$$ LANGUAGE plpgsql;

SELECT somar_tres_valores(10,20,30);

SELECT somar_tres_valores(1.1,2.2,3.3);

nome RECORD;

Assumem a estrutura da linha para a qual são atribuídas pelo comando SELECT ou FOR. A subestrutura da variável registro pode mudar toda vez que é usada em uma atribuição. Como conseqüência, antes de ser utilizada em uma atribuição a variável registro não possui subestrutura, e qualquer tentativa de acessar um de seus campos produz um erro em tempo de execução.

Deve ser observado que RECORD não é um tipo de dado real, mas somente um guardador de lugar. Deve-se ter em mente, também, que declarar uma função do PL/pgSQL como retornando o tipo record não é exatamente o mesmo conceito de variável registro, embora a função possa utilizar uma variável registro para armazenar seu resultado. Nos dois casos a verdadeira estrutura da linha é desconhecida quando a função é escrita, mas na função que retorna o tipo record a estrutura verdadeira é determinada quando o comando que faz a chamada é analisado, enquanto uma variável registro pode mudar a sua estrutura de linha em tempo de execução.

Tipos registro

IF-THEN

IF expressão_booleana THEN
    instruções
END IF;

As instruções IF-THEN são a forma mais simples de IF. As instruções entre o THEN e o END IF são executadas se a condição for verdade. Senão, são saltadas.

Exemplo:

IF v_id_usuario <> 0 THEN
    UPDATE usuarios SET email = v_email WHERE id_usuario = v_id_usuario;
END IF;

IF-THEN-ELSE

IF expressão_booleana THEN
    instruções
ELSE
    instruções
END IF;

As instruções IF-THEN-ELSE ampliam o IF-THEN permitindo especificar um conjunto alternativo de instruções a serem executadas se a condição for avaliada como falsa.

Exemplos:

IF v_contador > 0 THEN
    INSERT INTO contador_de_usuários (contador) VALUES (v_contador);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

Exercicio

1- Crie uma função que calcula o IMC

Cálculo do IMC

Para fazer o cálculo do IMC basta dividir seu peso em quilogramas pela altura ao quadrado (em metros). O número que será gerado deve ser comparado aos valores da tabela IMC para se saber se você está abaixo, em seu peso ideal ou acima do peso.

Por exemplo, se você pesa 60Kg e mede 1,67m, você deve utilizar a seguinte fórmula para calcular o IMC:

IMC = 60 ÷ 1,67²
IMC = 60 ÷ 2,78
IMC = 21,5

Tabela IMC

Abaixo de 18,5 = Você está abaixo do peso ideal

Entre 18,5 e 24,9 = Parabéns — você está em seu peso normal!

Entre 25,0 e 29,9 = Você está acima de seu peso (sobrepeso)

Entre 30,0 e 34,9 = Obesidade grau I

Entre 35,0 e 39,9 = Obesidade grau II

40,0 e acima = Obesidade grau III