CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; define uma função que as consultas podem chamar como se fosse nativa. O corpo vai entre marcas $$, em SQL puro ou em PL/pgSQL, a linguagem procedural do PostgreSQL. Já o CREATE PROCEDURE (PostgreSQL 11+) define uma rotina que você roda com CALL.
with_tax(50) usa a taxa padrão e retorna 60; with_tax(50, 0.1) retorna 55. Os argumentos também podem ser passados pelo nome: with_tax(50, rate => 0.1).
Escrever lógica em PL/pgSQL
Use LANGUAGE plpgsql quando a função precisar de variáveis, IF, loops ou tratamento de erros. As variáveis ficam numa seção DECLARE, e o código vai entre BEGIN e END:
Um pacote de 4 kg custa a base, 5; um pacote expresso de 20 kg custa (5 + 10 * 0.5) * 2 = 20. Dentro do PL/pgSQL, RAISE NOTICE 'value: %', cost; imprime uma mensagem e RAISE EXCEPTION aborta com um erro.
Quando SQL puro consegue expressar a lógica, prefira LANGUAGE sql: o planejador pode incorporar uma função SQL simples na consulta que a chama.
Retornar um conjunto de linhas
RETURNS TABLE (...) cria uma função que retorna linhas. Chame no FROM como uma tabela:
orders_of('ana') retorna os pedidos 1 e 3. RETURNS SETOF orders retorna linhas inteiras da tabela orders em vez das colunas listadas.
Desde o PostgreSQL 14, uma função SQL pode ter um corpo em SQL padrão em vez de uma string. O PostgreSQL guarda o corpo já interpretado e registra as tabelas e colunas usadas, então elas não podem ser removidas enquanto a função existir:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Criar uma stored procedure e rodar com CALL
Uma procedure não tem tipo de retorno; você roda com CALL. Ela pode alterar dados e, quando o CALL não está dentro de um bloco de transação explícito, pode fazer COMMIT no meio do caminho. É assim que se processa um trabalho grande em lotes.
A conta 1 termina com 70 e a conta 2 com 80. Uma transferência de 200 violaria a restrição CHECK da conta 1, e o erro desfaria os dois updates.
| Função | Procedure | |
|---|---|---|
| Roda com | SELECT f(...), em qualquer parte de uma consulta | só CALL p(...) |
| Retorna | um valor, uma linha ou um conjunto de linhas | nada (ou parâmetros OUT/INOUT) |
COMMIT / ROLLBACK dentro | não | sim, em PL/pgSQL, quando não é chamada dentro de um bloco de transação |
| Desde | sempre | PostgreSQL 11 |
Marcar a volatilidade: IMMUTABLE, STABLE, VOLATILE
O rótulo de volatilidade diz ao planejador o que ele pode supor sobre uma função. O padrão é VOLATILE.
IMMUTABLE: o resultado depende só dos argumentos e nunca muda (with_tax). Obrigatório para funções usadas numa expressão de índice.STABLE: o resultado pode mudar entre instruções, mas não dentro de uma, por exemplo uma função que lê tabelas (orders_of).VOLATILE: todo o resto, como funções que usamrandom()ou alteram dados. O PostgreSQL chama a função de novo para cada linha.
Um rótulo errado dá resultados errados: uma função IMMUTABLE que lê uma tabela pode retornar valores velhos de um índice ou de um plano em cache.
Substituir, sobrecarregar e excluir uma função
CREATE OR REPLACE FUNCTION muda o corpo de uma função existente, mas não o tipo de retorno; para isso, exclua a função antes. O PostgreSQL identifica uma função pelo nome e pelos tipos dos argumentos, então f(int) e f(text) são duas funções. Para excluir uma de duas funções com o mesmo nome, informe os tipos dos argumentos:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
O capítulo de PL/pgSQL documenta loops, cursores, exceções e SQL dinâmico com EXECUTE. Para rodar uma função automaticamente quando linhas mudam, associe a função a um trigger.
Perguntas frequentes
Qual a diferença entre função e procedure no PostgreSQL?
Uma função retorna um valor e é chamada dentro de uma consulta (SELECT f(x)). Uma procedure, adicionada no PostgreSQL 11, roda sozinha com CALL e pode fazer COMMIT ou ROLLBACK dentro do corpo; uma função sempre roda dentro da transação de quem a chamou.
O que significa $$ numa função do PostgreSQL?
$$ é o dollar quoting: marca o início e o fim de um literal de string, para que o corpo da função possa conter aspas simples sem escapar. Você também pode adicionar uma tag, como em $body$ ... $body$.
Como ver o código de uma função no PostgreSQL?
No psql, \df lista as funções e \sf name imprime a definição de uma função. Em SQL, SELECT pg_get_functiondef('name'::regproc); retorna a instrução CREATE OR REPLACE FUNCTION completa.
Uma função do PostgreSQL pode retornar várias linhas?
Pode. Declare com RETURNS TABLE (col type, ...) ou RETURNS SETOF type e chame no FROM como uma tabela: SELECT * FROM f(1);.