PostgreSQL supports user-defined functions, stored procedures, and triggers using multiple procedural languages. Functions vs Procedures Feature Function Procedure Returns value Yes No (but has OUT params) Transaction control No (runs in caller's TX) Yes (COMMIT/ROLLBACK) Called with SELECT / FROM CALL Available since Always PostgreSQL 11+ Procedural Languages PL/pgSQL — Default, SQL-like syntax PL/Python — Python functions inside PostgreSQL PL/V8 — JavaScript via V8 engine SQL — Pure SQL functions (simplest, inline-optimizable) Triggers Triggers automatically execute a function when a table event occurs: BEFORE / AFTER — Timing INSERT / UPDATE / DELETE / TRUNCATE — Event FOR EACH ROW / FOR EACH STATEMENT — Granularity Triggers are powerful for audit logging, data validation, computed…
Key Takeaways
Functions return values (use in SELECT); procedures support COMMIT/ROLLBACK.
Use LANGUAGE sql for simple functions — they are inline-optimizable.
Triggers automate audit logging, validation, and computed field updates.
PL/pgSQL exception blocks handle specific error conditions like uniqueviolation.