Decision Tree

Need return value?         → Use FUNCTION
Need transaction control?  → Use PROCEDURE
Need auto-update?          → Use TRIGGER
Need audit trail?          → Use trigger with AUDIT_LOG
Need error handling?       → Use EXCEPTION block

PL/pgSQL & Business Logic Guidelines

Encapsulate complex business logic within the database using Stored Procedures and Functions to ensure consistency, reducing round-trips and enforcing data integrity closer to the source.

1. Functions vs Procedures

1.1 When to use a Function (FUNCTION)