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

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

SELECT limpar_emp();

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

(1 linha)

INSERT INTO minha_tabela VALUES ($1);

INSERT INTO $1 VALUES (42);

31.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;

-- Sintaxe alternativa para o literal cadeia de caracteres:

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

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.

31.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
);

INSERT INTO emp VALUES('João',2200,21,point('(1,1)'));
INSERT INTO emp VALUES('José',4200,30,point('(2,1)'));

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
------+-------
 José |  8400
(1 linha)

Deve ser observada a utilização da sintaxe $1.salario para especificar o campo da linha passada como argumento. Deve ser observado, também, a utilização no comando SELECT do * para selecionar toda a linha corrente da tabela como um valor composto. Como alternativa, a linha da tabela pode ser referenciada usando apenas o nome da tabela, como mostrado abaixo:

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

mas esta forma de utilização está em obsolescência, uma vez que é propensa a causar confusão.

Algumas vezes é prático gerar o valor do argumento composto em tempo de execução. Isto pode ser feito através da construção ROW. Por exemplo, os dados passados para esta função poderiam estar na forma:

SELECT nome, dobrar_salario(ROW(nome, salario*1.1, idade, baia)) AS sonho
    FROM emp;

 nome | sonho
------+--------
 João | 4840.0
 José | 9240.0
(2 linhas)

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.0 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
  

Uma forma diferente para definir a mesma função seria:

CREATE OR REPLACE FUNCTION novo_empregado() RETURNS emp AS $$
    SELECT ROW('Nenhum', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

Neste caso foi escrito um comando SELECT que retorna apenas uma única coluna do tipo composto correto. Não é realmente o melhor nesta situação, mas é uma alternativa prática em algumas situações — por exemplo, se for necessário calcular o resultado chamando outra função que recebe como argumento o valor composto retornado por esta função.

Esta função poderia ser chamada diretamente de uma destas duas maneiras:

SELECT novo_empregado();

       novo_empregado
----------------------------
 (Nenhum,1000.0,25,"(2,2)")
(1 linha)

SELECT * FROM novo_empregado();

  nome  | salario | idade | baia
--------+---------+-------+-------
 Nenhum |  1000.0 |    25 | (2,2)
(1 linha)

A segunda forma está descrita com mais detalhes na Seção 31.4.3.

Quando se usa uma função que retorna um tipo composto, pode ser desejado apenas um campo (atributo) de seu resultado. Isto pode ser feito utilizando uma sintaxe do tipo:

SELECT (novo_empregado()).nome;

  nome
--------
 Nenhum
(1 linha)

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
LINHA 1: SELECT novo_empregado().nome;
                        ^

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
(1 linha)

SELECT emp.nome AS jovens FROM emp WHERE emp.idade < 30;

-- é o mesmo que:

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)

Uma outra maneira de utilizar uma função que retorna um tipo composto é chamá-la como uma função de tabela, conforme descrito abaixo.

31.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.

31.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 de resultados. [1]

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 de resultados 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.

31.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 31.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.