Noções Básicas de Funções

9 exemplos para você começar com funções PostgreSQL - 6 básicas e 3 intermediárias.

Pré-requisitos

CREATE no schema app.

# Desenvolvimento local com Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Exemplos Básicos

1. Função SQL

Corpo de uma única expressão SELECT.

CREATE OR REPLACE FUNCTION app.account_order_count(p_account_id bigint)
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
  SELECT count(*) FROM app.orders WHERE account_id = p_account_id;
$$;

• Pode ser inline pelo planejador quando marcado como STABLE/IMMUTABLE
• Sem variáveis procedurais
• Rápido para projeções simples

2. Função PL/pgSQL

Variáveis e IF/LOOP.

CREATE OR REPLACE FUNCTION app.is_vip(p_account_id bigint)
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $$
DECLARE v_total numeric;
BEGIN
  SELECT coalesce(sum(total),0) INTO v_total FROM app.orders WHERE account_id = p_account_id;
  RETURN v_total > 10000;
END;
$$;

• PL/pgSQL para ramificações e loops
• VOLATILE é o padrão se não especificado em SQL
• Mantenha os corpos pequenos

3. Chamada em Consulta

Use em SELECT e WHERE.

SELECT id, app.account_order_count(id) AS orders FROM app.accounts;

• Chamadas correlacionadas por linha podem ser lentas
• Considere reescrever com JOIN
• Marque a volatilidade corretamente

4. RETURNS TABLE

Formato de função que retorna um conjunto.

CREATE OR REPLACE FUNCTION app.big_orders(p_min numeric)
RETURNS TABLE (order_id bigint, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT id, total FROM app.orders WHERE total >= p_min;
$$;

• Funciona como uma view parametrizada
• O planejador pode fazer inline de funções SQL
• Conceda EXECUTE a roles

5. Exemplo IMMUTABLE

Funções puras habilitam expressões de índice.

CREATE OR REPLACE FUNCTION app.norm_email(e text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$ SELECT lower(trim(e)); $$;

• IMMUTABLE não deve depender do conteúdo do banco de dados ou do tempo
• Rótulo incorreto causa planos desatualizados
• Use para matemática simples de strings

6. DROP e Replace

CREATE OR REPLACE atualiza o corpo.

CREATE OR REPLACE FUNCTION app.norm_email(e text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT lower(trim(e)); $$;

• Não é possível alterar o tipo de retorno com OR REPLACE
• Use DROP FUNCTION se a assinatura mudar
• Acompanhe em migrações

Exemplos Intermediários

7. Procedimento vs Função

Procedimentos permitem controle de transação (CALL).

CREATE OR REPLACE PROCEDURE app.archive_old_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM app.orders WHERE created_at < now() - interval '2 years';
END;
$$;
CALL app.archive_old_orders();

• Procedimentos usam CALL, não SELECT
• Podem fazer COMMIT dentro de blocos de procedimento
• Funções não podem fazer commit

8. SECURITY INVOKER

O padrão executa como o chamador.

ALTER FUNCTION app.account_order_count(bigint) SECURITY INVOKER;

• Invoker respeita o RLS do chamador
• DEFINER eleva privilégios
• Audite funções definidoras com cuidado

9. Definir search_path em Definer

Proteja funções privilegiadas.

CREATE OR REPLACE FUNCTION app.admin_stat()
RETURNS int LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$ SELECT 1; $$;

• Previne o sequestro de objetos
• Veja o artigo sobre security definer
• Privilégio mínimo ainda é necessário

Versões da Pilha: Esta página foi escrita para PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x e PostGIS 3.5+.