Most developers interact with databases through individual SQL statements sent from application code. But databases also support server-side programming: named blocks of SQL and procedural logic that live inside the database. Stored procedures, functions, triggers, and views are the main tools. Each solves a different problem, and each has real trade-offs.
What Are Stored Procedures?#
A stored procedure is a named, reusable block of SQL (and procedural logic) stored in the database itself. You call it by name, passing parameters.
The entire logic executes on the database server. One network round-trip instead of four.
Benefits#
| Benefit | Explanation |
|---|---|
| Reduced network round-trips | Logic runs server-side; app sends one call |
| Reusable logic | Multiple apps / services call the same procedure |
| Security | Grant EXECUTE on procedure, not direct table access |
| Encapsulation | Business rules enforced at the data layer |
| Partial performance gain | Parsed and compiled once, not every call |
Security Model#
Stored Procedure Syntax: PostgreSQL (PL/pgSQL)#
PostgreSQL uses PL/pgSQL, a procedural extension of SQL with variables, loops, conditionals, and exception handling.
Basic Structure#
Example: Process an Order#
IN / OUT / INOUT Parameters#
Stored Procedures: MySQL Syntax#
MySQL stored procedures use a slightly different syntax.
Functions vs Procedures#
The key difference: functions return a value and can be used in SQL expressions. Procedures are called with CALL and are used for side effects.
| Feature | Procedure | Function |
|---|---|---|
| Return value | OUT parameters only | Single value or table |
| Use in SELECT | No | Yes |
| Called with | CALL | SELECT, WHERE, etc. |
| Transaction control | Yes (can COMMIT/ROLLBACK inside) | No (in PostgreSQL) |
| Side effects | Yes | Ideally no (but possible) |
Create Function Example: Compute Loyalty Points#
Table-Returning Functions#
Triggers#
Triggers automatically execute a function before or after a data-modification event (INSERT, UPDATE, DELETE) on a table.
Audit Log Trigger#
A common use case: record every change to sensitive data.
BEFORE Trigger: Validate and Transform Data#
Trigger Special Variables#
Views#
A view is a saved query with a name. You query it like a table, but it always runs the underlying query at execution time.
Creating Views#
Updatable vs Read-Only Views#
A view is updatable if it maps one-to-one to a single base table (no aggregates, no DISTINCT, no GROUP BY, no joins).
For non-updatable views that you still want to support DML, use INSTEAD OF triggers:
Materialised Views#
A regular view runs its query every time you query it. A materialised view stores the query result on disk like a table. It's fast to read but must be refreshed to reflect new data.
Refresh Strategies#
Regular View vs Materialised View#
| Regular View | Materialised View | |
|---|---|---|
| Storage | No (query only) | Yes (data on disk) |
| Query speed | Runs query every time | Instant (pre-computed) |
| Data freshness | Always current | Stale until refreshed |
| DML support | Sometimes | No |
| Use case | Simplify complex joins | Pre-aggregate expensive queries |
When NOT to Use Stored Procedures#
Stored procedures are not always the right answer. Know when to avoid them.
Tight Coupling to Database#
Stored procedures embed business logic inside the database. If you switch from PostgreSQL to MySQL, every procedure must be rewritten. The application and database become tightly coupled.
Hard to Test and Debug#
Version Control Challenges#
SQL files don't integrate naturally into git workflows. Migrations are one-directional. Rolling back a stored procedure change while keeping data intact is non-trivial.
The Rule of Thumb#
| Use stored procedures for | Avoid stored procedures for |
|---|---|
| Audit triggers | Core application business logic |
| Complex multi-step transactions that must be atomic | Simple CRUD operations |
| Security-sensitive operations (prevent SQL injection) | Logic that changes frequently |
| Database-level data validation | Logic that needs unit testing |
| Performance-critical operations with complex SQL | Microservice boundaries |
Putting It All Together#
A realistic example using multiple features together:
Summary#
- Stored procedures: server-side named SQL blocks. Use for multi-step transactions, audit logic, and security isolation. Avoid for core application business logic.
- Functions: like procedures but return values. Can be used in SELECT, WHERE, and JOIN. Use for reusable calculations.
- Triggers: automatically run code before/after data modifications. Use for audit logging and data validation. Keep triggers simple; complex trigger logic is hard to debug.
- Views: named saved queries. Simplify complex joins. Updatable if they map to a single table without aggregation.
- Materialised views: pre-computed query results stored on disk. Fast reads, must be refreshed. Use for expensive aggregations queried frequently.
- When to avoid procedures: when you need testability, portability, or frequent business logic changes. Keep that logic in the application layer.