33.4. Funções na linguagem de comando (SQL)

33.4.1. Funções SQL com tipos base

O tipo mais simples possível de função SQL não possui argumentos, e simplesmente retorna um tipo base como o integer:

CREATE FUNCTION um() RETURNS integer AS '
    SELECT 1 AS resultado;
' LANGUAGE SQL;

SELECT um();

  um
-----
   1
(1 linha)

Deve ser observado que foi definido um aliás de coluna dentro do corpo da função para o resultado da função (com o nome resultado), mas este aliás de coluna não é visível fora da função. Portanto, o rótulo do resultado é um em vez de resultado.

Definir funções SQL que recebem tipos base como argumentos é praticamente tão simples quanto este último exemplo. No exemplo abaixo deve ser observado que, dentro da função, os argumentos são referenciados como $1 e $2:

CREATE FUNCTION somar(integer, integer) RETURNS integer AS '
    SELECT $1 + $2;
' LANGUAGE SQL;

SELECT somar(1, 2) AS resposta;

 resposta
----------
        3
(1 linha)

Abaixo está mostrada uma função mais útil, que pode ser utilizada para realizar débitos em uma conta corrente no banco:

CREATE FUNCTION debitar (integer, numeric) RETURNS integer AS '
    UPDATE conta_corrente
        SET saldo = saldo - $2
        WHERE numero_da_conta = $1;
    SELECT 1;
' LANGUAGE SQL;

O usuário pode executar esta função para debitar R$100.00 da conta 17 da seguinte maneira:

SELECT debitar(17, 100.0);

Provavelmente, na prática seria desejado que a função retornasse um resultado mais útil do que a constante "1" e, portanto, uma definição mais realística seria

CREATE FUNCTION debitar (integer, numeric) RETURNS numeric AS '
    UPDATE conta_corrente
        SET saldo = saldo - $2
        WHERE numero_da_conta = $1;
    SELECT saldo FROM conta_corrente WHERE numero_da_conta = $1;
' LANGUAGE SQL;

que atualiza o saldo e retorna o novo saldo.

Qualquer coleção de comandos na linguagem SQL pode ser juntado e definido como uma função. Além de comandos SELECT, podem existir comandos de manipulação de dados (INSERT, UPDATE e DELETE), assim como outros comandos SQL. Entretanto, o comando final deve ser um SELECT retornando o que foi especificado como sendo o tipo retornado pela função. Como alternativa, se for desejado definir uma função SQL que realiza ações mas não retorna um valor útil, a função pode ser definida como retornando void. Neste caso, o corpo da função não deve terminar por um comamndo SELECT. Por exemplo, a função abaixo remove as linhas contendo salários negativos da tabela emp:

CREATE FUNCTION limpar_emp () RETURNS void AS '
    DELETE FROM emp
        WHERE emp.salario <= 0;
' LANGUAGE SQL;

SELECT limpar_emp();

 limpar_emp
-----------

(1 linha)

33.4.2. Funções SQL com tipos compostos

Ao escrever funções com argumentos de tipo composto, não se deve especificar apenas qual é o argumento desejado (como foi feito acima com $1 e $2), mas também qual é o atributo (campo) do argumento desejado. Por exemplo, supondo que emp seja uma tabela contendo dados dos empregados e, portanto, também o nome do tipo composto de cada linha da tabela, a função dobrar_salario mostarda abaixo calcula qual seria o salário de alguém caso este fosse dobrado:

CREATE TABLE emp (
    nome        text,
    salario     numeric,
    idade       integer,
    baia        point
);

CREATE FUNCTION dobrar_salario(emp) RETURNS numeric AS '
    SELECT $1.salario * 2 AS salario;
' LANGUAGE SQL;

SELECT nome, dobrar_salario(emp) AS sonho
    FROM emp
    WHERE emp.baia ~= point '(2,1)';

 nome | sonho
------+-------
 Bill |  8400

Deve ser observada a utilização da sintaxe $1.salario para especificar um campo da linha passada como argumento. Deve ser observado, também, como o comando SELECT utiliza o nome da tabela indicando toda a linha corrente da tabela como um valor composto. Como alternativa, a linha da tabela pode ser referenciada da seguinte maneira:

SELECT nome, dobrar_salario(emp.*) AS sonho
    FROM emp
    WHERE emp.cubiculo ~= point '(2,1)';

dando ênfase à natureza de linha.

Também é possível construir uma função que retorna um tipo composto. Abaixo está mostrado como exemplo uma função que retorna uma única linha da tabela emp:

CREATE FUNCTION novo_empregado() RETURNS emp AS '
    SELECT text ''Nenhum'' AS nome,
        1000 AS salario,
        25 AS idade,
        point ''(2,2)'' AS baia;
' LANGUAGE SQL;

Neste exemplo cada um dos atributos foi especificado através de um valor constante, mas estas constantes poderiam ser substituídas por algum valor calculado.

Devem ser observados dois fatos importantes sobre a definição da função:

• A ordem da lista de seleção da consulta deve ser exatamente a mesma em que as colunas aparecem na tabela associada ao tipo composto (Dar nome às colunas, como foi feito acima, é irrelevante para o sistema).
• Deve ser feita a conversão de tipo nas expressões para haver correspondência com a definição do tipo composto, ou acontecerá um erro deste tipo:

  ERRO: função declarada como retornando emp retorna varchar em vez de text para a coluna 1
  

A função que retorna uma linha (tipo composto) pode ser utilizada como uma função de tabela, conforme descrito abaixo. Também pode ser chamada no contexto de uma expressão SQL, mas somente quando é extraído um único atributo da linha ou passada toda a linha para outra função que aceita o mesmo tipo composto.

Abaixo está mostrado um exemplo da extração de um atributo de um tipo linha:

SELECT (novo_empregado()).nome;

 nome
--------
 Nenhum

Os parênteses adicionais são necessários para evitar que o analisador se confunda. Se for tentado fazer a consulta sem usar os parênteses adicionais será recebida uma mensagem de erro como esta:

SELECT novo_empregado().nome;
ERRO: erro de sintaxe em ou próximo a "." no caractere 24

Outra opção é utilizar a notação de função para extrair o atributo. A forma mais fácil de explicar isto é dizer que pode ser utilizada tanto a notação atributo(tabela) quanto a notação tabela.atributo, indiferentemente:

SELECT nome(novo_empregado());

  nome
--------
 Nenhum

-- o comando abaixo é o mesmo que:
-- SELECT emp.nome AS jovens FROM emp WHERE emp.idade < 30

SELECT nome(emp) AS jovens FROM emp WHERE idade(emp) < 30;

 jovens
--------
 João
(1 linha)

Outra maneira de utilizar uma função que retorna uma linha como resultado é passando o resultado desta função para outra função que recebe este tipo de linha como argumento:

CREATE FUNCTION obtem_nome(emp) RETURNS text AS
'SELECT $1.nome;'
LANGUAGE SQL;

SELECT obtem_nome(novo_empregado());

 obtem_nome
-----------
 Nenhum
(1 linha)

33.4.3. Funções SQL como fontes de tabela

Todas as funções SQL podem ser utilizadas na cláusula FROM da consulta, mas esta situação é particularmente útil no caso das funções que retornam tipos compostos. Se a função for definida como retornando um tipo base, a função de tabela produz uma tabela de uma coluna. Se a função for definida como retornando um tipo composto, a função de tabela produz uma coluna para cada atributo do tipo composto.

Abaixo segue um exemplo:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'João');
INSERT INTO foo VALUES (1, 2, 'José');
INSERT INTO foo VALUES (2, 1, 'Maria');

CREATE FUNCTION getfoo(int) RETURNS foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | João    | JOÃO
(1 linha)

Conforme mostrado neste exemplo, as colunas do resultado da função podem ser utilizadas da mesma maneira como se fossem colunas de uma tabela comum.

Deve ser observado que a função somente retornou uma linha. Isto se deve a não utilização de SETOF, que será descrito na próxima seção.

33.4.4. Funções SQL que retornam conjunto

Quando uma função SQL é declarada como retornando SETOF algum_tipo, a consulta SELECT no final da função é executada até o fim, e cada linha produzida é retornada como um elemento do conjunto resultado.

Esta funcionalidade normalmente é utilizada quando se chama a função na cláusula FROM. Neste caso, cada linha retornada pela função se torna uma linha da tabela vista pela consulta. Por exemplo, assumindo que a tabela foo possui o mesmo conteúdo mostrado acima, então:

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

Resultaria em:

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | João
     1 |        2 | José
(2 linhas)

Atualmente as funções que retornam conjunto também podem ser chamadas na lista de seleção da consulta. Para cada linha que a consulta gera por si própria é chamada a função que retorna conjunto, sendo gerada uma linha de saída para cada elemento do conjunto resultado da função. Entretanto, deve ser observado que esta funcionalidade está em obsolescência (deprecated), podendo ser removida em uma versão futura. Abaixo está mostrada, como exemplo, uma função retornando um conjunto colocada na lista de seleção:

CREATE FUNCTION listar_filhos(text) RETURNS SETOF text AS
'SELECT nome FROM nodos WHERE pai = $1'
LANGUAGE SQL;

SELECT * FROM nodos;

   nome    | pai
-----------+--------
 Topo      |
 Filho1    | Topo
 Filho2    | Topo
 Filho3    | Topo
 SubFilho1 | Filho1
 SubFilho2 | Filho1
(6 linhas)

SELECT listar_filhos('Topo');

 listar_filhos
--------------
 Filho1
 Filho2
 Filho3
(3 linhas)

SELECT nome, listar_filhos(nome) FROM nodos;

  nome  | listar_filhos
--------+---------------
 Top    | Filho1
 Top    | Filho2
 Top    | Filho3
 Filho1 | SubFilho1
 Filho1 | SubFilho2
(5 linhas)

Deve ser observado que no último comando SELECT não é mostrada nenhuma linha de saída para Filho2, Filho3, etc. Isto acontece porque listar_filhos retorna um conjunto vazio para estes argumentos e, portanto, não é gerada nenhuma linha de resultado.

33.4.5. Funções SQL polimórficas

As funções SQL podem ser declaradas como recebendo e retornando os tipos polimórficos anyelement e anyarray. Uma explicação mais detalhada sobre funções polimórficas pode ser vista na Seção 33.2.5 . Abaixo está mostrada a função polimórfica constroi_matriz, que constrói uma matriz a partir de dois elementos com tipo de dado arbitrário:

CREATE FUNCTION constroi_matriz(anyelement, anyelement) RETURNS anyarray AS '
    SELECT ARRAY[$1, $2];
' LANGUAGE SQL;

SELECT constroi_matriz(1, 2) AS intarray, constroi_matriz('a'::text, 'b') AS textarray;

 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 linha)

Deve ser observada a utilização da conversão de tipo 'a'::text para especificar que o argumento é do tipo text. Isto é necessário quando o argumento é apenas um literal cadeia de caracteres, uma vez que de outra forma seria tratado como sendo do tipo unknown, e uma matriz de unknown não é um tipo válido. Sem a conversão de tipo aconteceria um erro como este:

ERRO: não foi possível determinar o tipo de anyarray/anyelement porque a entrada possui o tipo "unknown"

É permitido ter argumentos polimórficos com tipo retornado estabelecido, mas o contrário não é permitido. Por exemplo:

CREATE FUNCTION eh_maior(anyelement, anyelement) RETURNS boolean AS '
    SELECT $1 > $2;
' LANGUAGE SQL;

SELECT eh_maior(1, 2);

 eh_maior
------------
 f
(1 linha)

CREATE FUNCTION funcao_invalida() RETURNS anyelement AS '
    SELECT 1;
' LANGUAGE SQL;

ERRO:  não foi possível determinar o tipo de dado do resultado
DETALHE:  Uma função retornando "anyarray" ou "anyelement" deve ter pelo menos
          um argumento do mesmo tipo.

-- As funções abaixo são válidas (N. do T.)

CREATE FUNCTION funcao_valida(anyelement) RETURNS anyelement AS '
    SELECT $1'
LANGUAGE SQL;

SELECT funcao_valida(1);

 funcao_valida
---------------
             1
(1 linha)

CREATE FUNCTION funcao_valida(anyarray) RETURNS anyarray AS '
    SELECT $1'
LANGUAGE SQL;

SELECT funcao_valida(array [1,2,3]);

 funcao_valida
---------------
 {1,2,3}
(1 linha)

-- A função abaixo não é válida (N. do T.)

CREATE FUNCTION funcao_invalida(anyelement) RETURNS anyarray AS '
    SELECT $1'
LANGUAGE SQL;

SELECT funcao_invalida(1);

ERRO: tipo retornado não corresponde na função declarada como retornando integer[]
DETALHE: Tipo retornado é integer.
CONTEXTO: função SQL "funcao_invalida" durante a execução