Functions Best Practices
Database functions centralize logic but hide behavior from application deploys.
How to Use This List
- Catalog all SECURITY DEFINER functions quarterly.
- Prefer SQL functions for simple transforms.
A - Design
- SQL function before PL/pgSQL. Planner can inline.
- Keep functions pure and small. Move orchestration to app when complex.
- Correct volatility labels. IMMUTABLE only when truly pure.
B - Security
- Default SECURITY INVOKER. RLS applies.
- DEFINER functions: SET search_path. Revoke PUBLIC execute.
- Grant EXECUTE minimally. Role-based access.
C - Testing
- Test in rolled-back transactions. Fast CI loops.
- pgTAP for regression suite. Version with migrations.
- Document side effects. Mutations vs reads.
FAQs
Are stored procedures replacing app code?
Only for tightly coupled data workflows; keep business rules visible in repo.
Related
- Functions Basics - intro
- Triggers Best Practices - when not to duplicate logic
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.