Menu

CREATE FUNCTION e stored procedures no PostgreSQL

O CREATE FUNCTION define uma função que você chama dentro das consultas, escrita em SQL ou PL/pgSQL. O CREATE PROCEDURE define uma rotina que você roda com CALL e que pode fazer commit no meio do caminho.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

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çãoProcedure
Roda comSELECT f(...), em qualquer parte de uma consultasó CALL p(...)
Retornaum valor, uma linha ou um conjunto de linhasnada (ou parâmetros OUT/INOUT)
COMMIT / ROLLBACK dentronãosim, em PL/pgSQL, quando não é chamada dentro de um bloco de transação
DesdesemprePostgreSQL 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 usam random() 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);.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar