Developing PL/pgSQL Functions

In this lesson, you will learn how to transition from running individual SQL queries to encapsulating complex business logic directly within the PostgreSQL database using PL/pgSQL functions.

Why Use PL/pgSQL?

While standard SQL is great for fetching or modifying data, it struggles with conditional logic, loops, and multi-step processes. If you have a business rule—like "apply a discount if a user has ordered more than five times"—running three separate SQL queries from your application code can be slow due to network latency and redundant parsing. PL/pgSQL allows you to bundle these steps into a single, pre-compiled object that executes entirely within the database engine.

Building Your First Function

A PL/pgSQL block is defined using the CREATE FUNCTION command. Think of it as a container where you can define variables, handle errors, and manage control flow.

Here is a simple example that updates a customer's status based on their purchase count:

CREATE OR REPLACE FUNCTION promote_customer(customer_id INT) 
RETURNS TEXT AS $$
DECLARE
    order_count INT;
BEGIN
    -- Business Logic: Check total orders
    SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = customer_id;

    IF order_count > 5 THEN
        UPDATE users SET status = 'VIP' WHERE id = customer_id;
        RETURN 'Customer promoted to VIP';
    ELSE
        RETURN 'Customer status unchanged';
    END IF;
END;
$$ LANGUAGE plpgsql;

How It Works

1. DECLARE: This section is for defining local variables that exist only during the function's execution.
2. BEGIN...END: This block holds your procedural code.
3. Encapsulation: By moving this UPDATE logic inside the database, you ensure that the same rule is applied consistently, regardless of whether the request comes from a web app, a mobile backend, or a manual admin script.

Because PL/pgSQL is tightly integrated with the PostgreSQL execution engine, it avoids the "chattiness" of executing multiple round-trips to the database. It handles the procedural flow locally, reducing overhead and enforcing business rules at the data layer where they are safest.

Key Takeaway: PL/pgSQL allows you to transform fragmented SQL queries into structured, reusable procedural logic that executes entirely within the database for better performance and consistency.