SQL Stored Procedures & Functions: Programming the Mirror

In a traditional application stack, your Python, Go, or Java backend asks the database for $10,000$ rows of raw data, iterates through them to calculate a "User Loyalty Score," and then sends $10,000$ individual UPDATE statements back to the database.

The Problem: You just moved $20$MB of data over a network line twice just to calculate one number. You've hit the "Latency Wall."
The Solution: Build the logic Inside the database. The database iterates over its own internal pages at RAM speed, calculates the result, and commits the changes without a single bit of data ever leaving the CPU cache of the database server.

1. Function vs. Procedure: The 2026 Split

To an amateur, they look the same. To a Senior Architect, they are completely different animals.

The Function (`RETURNS ...`)

Functions are "Computational Units." They are designed to take inputs and give you a predictable output.

Immutability: You can mark a function as IMMUTABLE (it always returns the same result for the same input), allowing the database to Cache the Result in the Plan Buffer.
Transactional Limit: Functions cannot manage transactions. You cannot call COMMIT or ROLLBACK inside a function because it is usually executed as part of a larger SELECT or UPDATE statement that is already inside a transaction.

The Procedure (`CALL ...`)

Procedures are "Workflow Units." They are designed to perform complex, multi-step actions.

Transaction Control: Unlike functions, procedures can perform their own COMMIT or ROLLBACK. This is critical for long-running migration scripts where you want to commit every $1,000$ rows to avoid blowing up the WAL (Write Ahead Log).
Execution: Procedures are invoked with the CALL keyword and cannot be used inside a standard SELECT list.

2. PL/pgSQL: The Hardware-Mirror Programming Language

PostgreSQL uses PL/pgSQL. It isn't just a query language; it is a full, procedural programming language with variables, conditional logic, loops, and exception handling.

2.1 The Physics of Context Switching: App vs. DB

Why is code inside the database so much faster than code in Go or Python?

The Context Mirror: When your app talks to the database, it uses a Socket. Every query requires a "Context Switch" where the OS pauses your app, wakes up the network driver, sends bytes, and then wakes up the database.
The CPU Isolation Reality: Inside a Stored Procedure, there is Zero Context Switching. The logic runs as a native C-function within the database's own process space.
The Hardware Advantage: The procedure has direct access to the Shared Buffers (RAM). It doesn't "Fetch" data over a wire—it simply reads the memory address that already contains the database page. This is the difference between "Ordering a book" and "Already being in the library."

Anatomy of a High-Performance Function

sql

Architect's Insight: By using FOR UPDATE, the function locks the rows it is visiting, preventing "Race Conditions" where two processes try to pay the same user at the same time. Doing this in Go or Python would require complex distributed locking; in SQL, it's just one keyword.

3. The Security Mirror: DEFINER vs. INVOKER

One of the most powerful (and dangerous) features of Stored Procedures is Privilege Escalation.

SECURITY INVOKER (Default): The function runs with the permissions of the person who called it. If a low-level user calls it, they can only see what they already have access to.
SECURITY DEFINER: The function runs with the permissions of the person who created it (usually the Superuser).

The Use Case for DEFINER

Imagine a reset_password() function. A user shouldn't have access to the passwords table, but they need to be able to update their own row. You create a SECURITY DEFINER function that performs strict validation and then performs the update.

The Warning: You must be extremely careful to avoid SQL Injection inside a SECURITY DEFINER function, as a malicious user could exploit it to gain full database control.

4. Debugging and Instrumentation: RAISE LOG

The biggest complaint about stored logic is that it's a "Black Box." If it fails, how do you see what happened?

The Diagnostic Protocol

Use the RAISE keyword to send messages to the client or the server log.

RAISE NOTICE 'Processing user %', user_id; (Sends to the user's terminal).
RAISE EXCEPTION 'Balance cannot be negative'; (Triggers a rollback and returns an error code).
RAISE LOG 'Performance audit: query took % ms', delta; (Sends to the database engine's log for permanent auditing).

5. Caching and Plan Reuse (The Performance Mirror)

When you run a standard SQL query, the database has to "Parse" it, "Plan" it, and then "Execute" it every single time. With a Stored Function, the database Caches the Execution Plan.

5.1 The Atomic State Machine: Architecting logic with Procedures

Beyond simple math, procedures allow you to build State Machines that guarantee data transitions are valid.

The Concept: Imagine a "Subscription" that can only move from ACTIVE to PAUSED or CANCELLED.
The Logic: Instead of your app checking the status and then sending an update (a classic "Time-of-Check to Time-of-Use" race condition), you call a transition_subscription_status() procedure.
The Guarantee: The procedure performs a LOCK FOR UPDATE, verifies the current status, and only then performs the transition. Because this is atomic, you can never have two conflicting status changes happen at once.

6. Case Study: The "Inventory Explosion" (Phase 4 Logic)

A logistics company had an application that updated the "Stock Count" in $50$ different warehouses after a large shipment.

The Application Approach: The app sent 50 separate UPDATE commands. Total time: $2.5$ seconds due to network latency.
The Stored Procedure Approach: They built an update_global_stock() procedure that took a JSON array as input.
The Result: The procedure processed all 50 updates internally in $12$ms. The ship-to-delivery pipeline became $200x$ more efficient overnight.

7. Performance Lab: Tuning the Logic Center

Logic Gravity: If your logic requires reading more than $100$ rows from the same table to calculate an update, move it into a SQL Function.
Procedure for Scale: Use Procedures with internal COMMIT points for database migrations involving millions of rows.
Default to Invoker: Only use SECURITY DEFINER when absolutely necessary, and always set a search_path.
No Spaghetti Logic: Keep functions small and focused.

7.1 Procedural Parallelism: When the Engine Branches

Can a stored procedure run in parallel?

The Reality: In Postgres, a single call to a PL/pgSQL function runs on a single CPU core. However, the SQL queries inside the function can still be parallelized by the engine.
The Optimization: If your function performs a massive SUM() over a partitioned table, the database can still launch 8 worker threads to do the math, even as your procedural logic (the loop) stays on the main thread.
The Tip: Set COST high on your functions (e.g., COST 1000) to tell the optimizer that this logic is expensive, encouraging it to search for parallel plans more aggressively.

8. Summary: The In-DB Logic Checklist

Logic Gravity: Move code to the data to eliminate the "Network Latency Wall."
Context Switching Avoidance: Use stored logic to minimize the socket-crossing cost between your app and the DB.
Procedure for Scale: Use CALL for multi-step workflows with transaction control; use SELECT functions for math.
Security Sovereignty: Implement SECURITY DEFINER only as a surgical tool for authorized privilege escalation.
Audit Awareness: Use RAISE LOG to turn your database into a transparent, observable execution engine.

Programmable SQL is the Command Center of your infrastructure. By mastering the distinction between functions and procedures and the efficiency of plan caching, you gain the power to build systems that are fundamentally faster, more consistent, and more secure. You graduate from "Storing data" to "Architecting High-Order Logic."

Phase 23: Logic Mastery Checklist

Refactor a multi-step update sequence into a single SQL PROCEDURE.
Create an IMMUTABLE function for a core business calculation and verify it is cached.
Implement strict error handling with EXCEPTION blocks and RAISE notices.
Advanced Goal: Implement an atomic State Machine procedure for a mission-critical business transition.
Audit for socket-crossing bottlenecks and migrate high-latency loops to PL/pgSQL.

Part of the SQL Mastery Course — engineering the logic.

SQL Stored Procedures and Functions: Procedural Logic