Learn SQL with PostgreSQL - Stored Procedures, Functions (PL/pgSQL) & Triggers

#Introduction

Welcome to episode 13 of the Learn SQL with PostgreSQL series! So far, all the logic lives in the application and the database is just a "data warehouse". But there are times when it's better to put logic inside the database itself: validations that must be guaranteed, automations that no application may forget, or complex calculations too expensive to shuttle back and forth to the application.

In this episode, we'll cover three main tools for "coding inside the database": User-Defined Functions (UDF) using PostgreSQL's internal language called PL/pgSQL, stored procedures and their differences from functions (especially regarding transactions), and database triggers for automation — with two classic case studies: automatic updated_at columns and audit logging.

#User-Defined Functions (UDF) with PL/pgSQL

A function in PostgreSQL is a logic block that accepts parameters, processes, and returns a value. It can be written in various languages (SQL, Python, C), but the most common and native one is PL/pgSQL.

#Basic Function Syntax

CREATE FUNCTION tambah(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

Call it with SELECT tambah(3, 4) and the result is 7. Note the structure: LANGUAGE plpgsql determines the language, and the body is wrapped in $$ ... $$ (dollar quoting) which replaces single quotes so strings can be written inside safely.

#Function with More Complex Logic

A function can contain variables, branching, and queries:

CREATE FUNCTION cek_stok(pid UUID, qty INTEGER)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    stok_tersedia INTEGER;
BEGIN
    SELECT stock INTO stok_tersedia
    FROM inventory
    WHERE product_id = pid;
 
    IF stok_tersedia IS NULL THEN
        RETURN FALSE;
    ELSIF stok_tersedia >= qty THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$;

DECLARE declares variables, SELECT ... INTO fills a variable from a query, and the IF/ELSIF/ELSE block handles the logic. This is the function we'll use in the e-commerce case study in episode 20.

#Stored Procedures: Functions That Can Manage Transactions

The key differences between functions and stored procedures in PostgreSQL:

This transaction difference is the most fundamental one. A function runs inside its caller's transaction and cannot control it. A procedure can run COMMIT or ROLLBACK inside its body — useful for flows that must save partial results:

CREATE PROCEDURE proses_order(pid UUID)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders SET status = 'processing' WHERE id = pid;
    COMMIT;
END;
$$;

Call the procedure with the CALL command:

CALL proses_order('1c2d3e4f-0000-0000-0000-000000000001');

#Database Triggers: Execution Automation

A trigger is a function that runs automatically when a certain event occurs on a table. The events can be INSERT, UPDATE, DELETE, or TRUNCATE, and the timing can be BEFORE (before the operation) or AFTER (after the operation), per row (FOR EACH ROW) or per statement (FOR EACH STATEMENT).

#Case Study 1: Automatic updated_at Column

The classic problem: applications forget to update updated_at. A trigger solves it once and for all:

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

With this trigger, every UPDATE on the users table automatically fills updated_at with the current time — no matter who executes it, from which application, or through which query. NEW is the new row (result of the update) that we can modify before it's stored.

#Case Study 2: Audit Logging

The second pattern is very valuable: recording every data change into an audit table. It's a trail required for compliance, debugging, and forensics:

CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    row_id UUID NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE FUNCTION log_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, row_id)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.id);
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_audit();

The trigger function uses special variables: TG_TABLE_NAME (table name), TG_OP (operation: INSERT/UPDATE/DELETE). For a DELETE trigger, NEW doesn't exist — use OLD to get the old row's id.

#Triggers and Constraints

It's important to place responsibilities correctly: constraints handle simple, static validations (CHECK, FOREIGN KEY, UNIQUE), while triggers handle dynamic logic that needs broader context — like reading other tables, or modifying other rows as a side effect. Don't create a BEFORE INSERT trigger for a validation that could actually be a CHECK — it's slower and easier to leak.

#Closing

Key takeaways:

• Functions return a value and can be called in queries; procedures are called with CALL and can control transactions.
• PL/pgSQL adds variables, branching, and loops on top of SQL.
• Triggers automatically run a function on DML events (BEFORE/AFTER, FOR EACH ROW).
• The updated_at and audit log triggers are mandatory patterns for production databases.
• Triggers add cost to every operation — use them sparingly and document them.

In the next episode, episode 14, we get into performance: Deep Dive Indexing Strategies — from why indexing turns O(N) searches into O(log N), the B-Tree, Hash, GIN, and BRIN index types, to advanced techniques like partial index, expression index, and composite index.