Escrever Funções PL/pgSQL

Introdução

O PostgreSQL é mais do que um motor de SGBDR. É uma plataforma de desenvolvimento. Disponibiliza uma linguagem de programação muito poderosa e flexível chamada PL/pgSQL. Usando esta linguagem, pode escrever as suas próprias funções definidas pelo utilizador para alcançar níveis de abstração e cálculos processuais que seriam difíceis de conseguir com SQL simples (e por vezes impossíveis de conseguir sem mudar de contexto para a aplicação).

Escrever funções

No esquema public, clique com o botão direito no nó Functions e clique em Create Function (Criar Função). Abrirá um separador interior de Consulta SQL, já contendo um Modelo SQL para o ajudar a criar a sua primeira função PL/pgSQL.

Pode consultar a documentação do PostgreSQL sobre como escrever funções definidas pelo utilizador. Basta clicar com o botão direito no nó Functions e clicar em Doc: Functions — a página relevante da documentação do PostgreSQL abre no seu browser (o browser predefinido do sistema na aplicação de ambiente de trabalho, ou um novo separador quando executado como OmniDB Server baseado no browser).

Por agora, vamos substituir este modelo SQL inteiramente pelo código-fonte abaixo:

CREATE OR REPLACE FUNCTION public.fnc_count_vowels (p_input text)
RETURNS integer LANGUAGE plpgsql AS
$function$
DECLARE
  str text;
  ret integer;
  i   integer;
  len integer;
BEGIN
  str := upper(p_input);
  ret := 0;
  i := 1;
  len := length(p_input);
  WHILE i <= len LOOP
    IF substr(str, i, 1) in ('A', 'E', 'I', 'O', 'U') THEN
      ret := ret + 1;
    END IF;
    i := i + 1;
  END LOOP;
  RETURN ret;
END;
$function$

Isto criará uma função chamada fnc_count_vowels dentro do esquema public. Esta função recebe um argumento de texto chamado p_input e conta quantas vogais existem nessa string. Depois devolve essa contagem.

Para criar a função, execute o comando no separador interior de Consulta SQL. Se for bem-sucedido, a função aparecerá sob o nó da árvore Functions (pode atualizá-lo clicando com o botão direito e depois clicando em Refresh). Ao expandir o nó da função também, pode ver o seu tipo de retorno e o seu argumento.

Agora vamos executar esta nova função pela primeira vez. Abra um separador interior simples de Consulta SQL e execute a seguinte consulta SQL:

SELECT public.fnc_count_vowels('The quick brown fox jumps over the lazy dog.')

Repare como a consulta devolve um único valor, contendo o número de vogais no texto.

Ao clicar com o botão direito no nó da função, pode ver que existem ações para a editar, selecionar e eliminar. Como provavelmente adivinhou, cada ação abrirá separadores interiores de Consulta SQL com modelos SQL práticos.