Stored Procedures, Functions, Triggers & Views

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 logic in the application layer.