# PL/pgSQL for People Who Already Know SQL

*A fast revision sheet: procedural PostgreSQL, from variables to triggers, and when* ***not*** *to use it.*

> Postgres's procedural language is **PL/pgSQL**, a close cousin of Oracle's PL/SQL. Syntax differs in places (`$$` bodies, `RETURNS TABLE`, no packages), and the concepts map almost 1:1. Everything below runs on PostgreSQL 14+.

---

## 0\. Schema Used Throughout

One schema, five tables, deliberately boring.

### ER diagram (paste into [mermaid.live](https://mermaid.live))

```mermaid
erDiagram
    customers ||--o{ orders : places
    orders ||--|{ order_items : contains
    products ||--o{ order_items : "sold as"

    customers {
        int id PK
        text name
        text email UK
        text country
        timestamptz created_at
    }
    products {
        int id PK
        text name
        text category
        numeric price
        int stock
    }
    orders {
        int id PK
        int customer_id FK
        text status
        numeric total
        timestamptz created_at
    }
    order_items {
        int id PK
        int order_id FK
        int product_id FK
        int qty
        numeric unit_price
    }
    audit_log {
        bigint id PK
        text table_name
        text action
        int row_id
        jsonb old_data
        jsonb new_data
        timestamptz changed_at
    }
```

`audit_log` is standalone on purpose: it is filled by triggers only.

### DDL

```sql
CREATE SCHEMA IF NOT EXISTS shop;
SET search_path = shop, public;

CREATE TABLE customers (
    id          serial PRIMARY KEY,
    name        text NOT NULL,
    email       text NOT NULL UNIQUE,
    country     text NOT NULL DEFAULT 'US',
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id        serial PRIMARY KEY,
    name      text NOT NULL,
    category  text NOT NULL,
    price     numeric(10,2) NOT NULL CHECK (price >= 0),
    stock     int NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    id           serial PRIMARY KEY,
    customer_id  int NOT NULL REFERENCES customers(id),
    status       text NOT NULL DEFAULT 'new'
                 CHECK (status IN ('new','paid','shipped','cancelled')),
    total        numeric(12,2) NOT NULL DEFAULT 0,
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id          serial PRIMARY KEY,
    order_id    int NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  int NOT NULL REFERENCES products(id),
    qty         int NOT NULL CHECK (qty > 0),
    unit_price  numeric(10,2) NOT NULL
);

CREATE TABLE audit_log (
    id          bigserial PRIMARY KEY,
    table_name  text NOT NULL,
    action      text NOT NULL,
    row_id      int,
    old_data    jsonb,
    new_data    jsonb,
    changed_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON order_items (product_id);
```

### Dummy data

```sql
INSERT INTO customers (name, email, country) VALUES
  ('Ayesha Rahman', 'ayesha@example.com', 'BD'),
  ('Liam Carter',   'liam@example.com',   'US'),
  ('Sofia Rossi',   'sofia@example.com',  'IT'),
  ('Noah Smith',    'noah@example.com',   'UK');

INSERT INTO products (name, category, price, stock) VALUES
  ('Mechanical Keyboard', 'peripherals', 89.90, 40),
  ('27" Monitor',         'displays',   229.00, 15),
  ('USB-C Hub',           'peripherals', 34.50, 3),
  ('Webcam 1080p',        'peripherals', 59.00, 25),
  ('Standing Desk',       'furniture',  420.00, 5);

INSERT INTO orders (customer_id, status) VALUES
  (1,'paid'), (2,'new'), (1,'shipped'), (3,'paid');

INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
  (1,1,1,89.90), (1,3,2,34.50),
  (2,2,1,229.00),
  (3,4,2,59.00), (3,1,1,89.90),
  (4,5,1,420.00);

-- Seed totals (the trigger in section 11 maintains them afterwards)
UPDATE orders o SET total = s.t
FROM (SELECT order_id, sum(qty*unit_price) t FROM order_items GROUP BY 1) s
WHERE s.order_id = o.id;
```

---

## 1\. Variables and Data Types

```sql
DO $$
DECLARE
    c_vat    CONSTANT numeric := 0.15;                 -- immutable
    v_cnt    int NOT NULL DEFAULT 0;
    v_price  products.price%TYPE;                      -- follows column type
    v_tags   text[] := ARRAY['a','b'];
    v_cfg    jsonb  := '{"retries": 3}';
    v_row    products%ROWTYPE;                         -- whole-row type
BEGIN
    SELECT * INTO v_row FROM products WHERE id = 1;
    v_price := v_row.price * (1 + c_vat);
    RAISE NOTICE 'gross=%, retries=%, tag1=%', v_price, v_cfg->>'retries', v_tags[1];
END $$;
```

- Prefer `%TYPE` / `%ROWTYPE`, so column changes don't break the code.
- `:=` and `=` both assign; stick to one.
- `SELECT ... INTO` takes the first row silently. Use `INTO STRICT` to enforce exactly one.

---

## 2\. IF / ELSE and CASE

```sql
DO $$
DECLARE v_stock int;
BEGIN
    SELECT stock INTO STRICT v_stock FROM products WHERE id = 3;

    IF v_stock = 0 THEN
        RAISE NOTICE 'out of stock';
    ELSIF v_stock < 5 THEN
        RAISE NOTICE 'low stock: %', v_stock;
    ELSE
        RAISE NOTICE 'ok';
    END IF;

    -- Searched CASE statement: raises CASE_NOT_FOUND if nothing matches and no ELSE
    CASE
        WHEN v_stock < 5  THEN RAISE NOTICE 'reorder now';
        WHEN v_stock < 20 THEN RAISE NOTICE 'reorder soon';
        ELSE                   RAISE NOTICE 'fine';
    END CASE;
END $$;

-- CASE *expression* in plain SQL (different thing, no END CASE)
SELECT name, CASE WHEN stock < 5 THEN 'low' ELSE 'ok' END AS level FROM products;
```

Watch out: a PL/pgSQL `CASE` statement without `ELSE` throws. The SQL `CASE` expression returns `NULL`.

---

## 3\. FOR and WHILE Loops

```sql
DO $$
DECLARE
    r record;
    i int := 0;
BEGIN
    FOR n IN 1..3 LOOP                        -- integer range (REVERSE n IN ..., BY 2 supported)
        RAISE NOTICE 'n=%', n;
    END LOOP;

    FOR r IN SELECT id, name FROM products ORDER BY id LOOP   -- query loop
        CONTINUE WHEN r.id = 2;
        RAISE NOTICE '% - %', r.id, r.name;
    END LOOP;

    WHILE i < 3 LOOP
        i := i + 1;
    END LOOP;

    LOOP                                      -- bare loop
        i := i - 1;
        EXIT WHEN i <= 0;
    END LOOP;

    FOREACH i IN ARRAY ARRAY[10,20,30] LOOP   -- array iteration
        RAISE NOTICE 'i=%', i;
    END LOOP;
END $$;
```

A `FOR ... IN query` loop uses an implicit cursor, so it streams rows in batches instead of loading the whole result.

---

## 4\. Exception Handling

```sql
CREATE OR REPLACE FUNCTION safe_decrement_stock(p_product int, p_qty int)
RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
    v_state text; v_msg text; v_detail text;
BEGIN
    UPDATE products SET stock = stock - p_qty WHERE id = p_product;
    IF NOT FOUND THEN
        RAISE no_data_found USING MESSAGE = format('product %s not found', p_product);
    END IF;
    RETURN 'ok';
EXCEPTION
    WHEN check_violation THEN                      -- stock >= 0 CHECK
        RETURN 'insufficient stock';
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE,
                                v_msg   = MESSAGE_TEXT,
                                v_detail = PG_EXCEPTION_DETAIL;
        RAISE LOG 'sqlstate=% msg=% detail=%', v_state, v_msg, v_detail;
        RAISE;                                     -- re-throw original error
END $$;

SELECT safe_decrement_stock(3, 100);   -- insufficient stock
SELECT safe_decrement_stock(99, 1);    -- raises no_data_found
```

- An `EXCEPTION` block opens a **subtransaction** (savepoint). Cheap once, expensive inside hot loops. Don't wrap every iteration "just in case".
- On error, all changes inside that block are rolled back.
- Custom errors: `RAISE EXCEPTION 'msg' USING ERRCODE = 'P0001', HINT = '...'`.
- `ASSERT cond, 'msg'` is a cheap debug check (disable with `plpgsql.check_asserts`).

---

## 5\. Functions vs Stored Procedures

|  | `FUNCTION` | `PROCEDURE` |
| --- | --- | --- |
| Invoked with | `SELECT f()` | `CALL p()` |
| Returns | value / set / table | nothing (only `INOUT` args) |
| Transaction control | no `COMMIT`/`ROLLBACK` | yes (if not called inside an outer explicit transaction) |
| Usable in SQL queries | yes | no |
| Volatility hints | `IMMUTABLE` / `STABLE` / `VOLATILE` | n/a |

```sql
-- Function: usable inside queries, mark volatility honestly
CREATE OR REPLACE FUNCTION gross_price(p_net numeric, p_vat numeric DEFAULT 0.15)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT round(p_net * (1 + p_vat), 2) $$;

SELECT name, gross_price(price) FROM products;

-- Procedure: batch job that commits in chunks
CREATE OR REPLACE PROCEDURE archive_cancelled_orders(p_batch int DEFAULT 1000)
LANGUAGE plpgsql AS $$
DECLARE v_n int;
BEGIN
    LOOP
        DELETE FROM orders
        WHERE id IN (SELECT id FROM orders WHERE status = 'cancelled' LIMIT p_batch);
        GET DIAGNOSTICS v_n = ROW_COUNT;
        COMMIT;                              -- legal only in procedures
        EXIT WHEN v_n = 0;
    END LOOP;
END $$;

CALL archive_cancelled_orders();
```

Tip: a one-statement `LANGUAGE sql` function can often be **inlined** by the planner. A PL/pgSQL function is always a black box.

---

## 6\. IN, OUT, INOUT Parameters

```sql
-- OUT params define the result row shape
CREATE OR REPLACE FUNCTION order_summary(
    IN  p_order_id int,
    OUT item_count int,
    OUT total      numeric
) LANGUAGE plpgsql STABLE AS $$
BEGIN
    SELECT count(*), COALESCE(sum(qty * unit_price), 0)
      INTO item_count, total
      FROM order_items WHERE order_id = p_order_id;
END $$;

SELECT * FROM order_summary(1);              -- item_count | total

-- INOUT in a procedure: pass NULL placeholder, read the value back
CREATE OR REPLACE PROCEDURE place_order(
    p_customer int, p_product int, p_qty int, INOUT p_order_id int DEFAULT NULL
) LANGUAGE plpgsql AS $$
DECLARE v_price numeric;
BEGIN
    SELECT price INTO STRICT v_price FROM products WHERE id = p_product;
    INSERT INTO orders (customer_id) VALUES (p_customer) RETURNING id INTO p_order_id;
    INSERT INTO order_items (order_id, product_id, qty, unit_price)
    VALUES (p_order_id, p_product, p_qty, v_price);
END $$;

CALL place_order(2, 4, 1, NULL);             -- returns the new order id
```

- Functions with one `OUT` param return a scalar, multiple `OUT` params return a record.
- `VARIADIC` and `DEFAULT` params exist. Beware overload ambiguity when mixing them.

---

## 7\. RECORD and ROW Types

```sql
-- Custom composite type
CREATE TYPE price_band AS (lo numeric, hi numeric);

DO $$
DECLARE
    r_prod  products%ROWTYPE;               -- fixed structure = table row
    r_any   record;                         -- structure decided at first assignment
    b       price_band := ROW(50, 250);
BEGIN
    SELECT * INTO r_prod FROM products WHERE id = 1;

    FOR r_any IN SELECT name, price FROM products WHERE price BETWEEN b.lo AND b.hi LOOP
        RAISE NOTICE '% costs %', r_any.name, r_any.price;
    END LOOP;

    r_prod.stock := r_prod.stock + 10;
    UPDATE products SET (name, category, price, stock) =
        (r_prod.name, r_prod.category, r_prod.price, r_prod.stock)
    WHERE id = r_prod.id;
END $$;
```

- `record` has no structure until assigned. Accessing fields before that throws.
- Every table automatically defines a composite type of the same name, so `RETURNS SETOF products` works.
- `to_jsonb(r_any)` / `jsonb_populate_record(NULL::products, j)` convert between rows and JSON.

---

## 8\. Cursors

```sql
-- Explicit cursor with a parameter
DO $$
DECLARE
    c  CURSOR (p_cat text) FOR SELECT id, name FROM products WHERE category = p_cat;
    r  record;
BEGIN
    OPEN c('peripherals');
    LOOP
        FETCH c INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '%', r.name;
    END LOOP;
    CLOSE c;
END $$;

-- Refcursor: hand a result set to the client, which fetches it lazily
CREATE OR REPLACE FUNCTION open_big_orders(p_min numeric) RETURNS refcursor
LANGUAGE plpgsql AS $$
DECLARE cur refcursor := 'big_orders';
BEGIN
    OPEN cur FOR SELECT * FROM orders WHERE total >= p_min;
    RETURN cur;
END $$;

BEGIN;
SELECT open_big_orders(100);
FETCH 2 FROM big_orders;
COMMIT;
```

- 90% of the time, `FOR r IN SELECT ...` is all you need.
- Explicit cursors matter for **paging through huge results**, `MOVE`, `FETCH n`, and `WITH HOLD` across transactions.
- Cursors live inside a transaction unless declared `WITH HOLD` (which materializes the result at commit).

---

## 9\. Dynamic SQL with `EXECUTE`

```sql
-- Dynamic identifiers + parameterized values
CREATE OR REPLACE FUNCTION top_products(p_order_by text, p_limit int DEFAULT 3)
RETURNS SETOF products
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT * FROM products ORDER BY %I DESC LIMIT $1', p_order_by)
        USING p_limit;
END $$;

SELECT * FROM top_products('price');

-- Single-value result
CREATE OR REPLACE FUNCTION row_count(p_table text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', p_table) INTO n;
    RETURN n;
END $$;
```

The rule of thumb:

| Placeholder | Use for |
| --- | --- |
| `%I` | identifiers (table, column names): quoted safely |
| `%L` | literals: quoted safely (or `NULL`) |
| `$1` + `USING` | **values**: preferred over `%L`, avoids re-parsing and injection |

Never build SQL with `||` and user input. `EXECUTE` plans on every call, while static SQL in PL/pgSQL gets cached plans.

---

## 10\. Returning `SETOF` and `TABLE`

```sql
-- SETOF an existing row type (pure SQL, can be inlined)
CREATE OR REPLACE FUNCTION low_stock(p_threshold int DEFAULT 5)
RETURNS SETOF products
LANGUAGE sql STABLE AS
$$ SELECT * FROM products WHERE stock <= p_threshold $$;

-- RETURNS TABLE: ad-hoc shape, columns become OUT params
CREATE OR REPLACE FUNCTION customer_orders(p_customer int)
RETURNS TABLE (order_id int, status text, total numeric, items bigint)
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.status, o.total, count(oi.id)
    FROM orders o
    LEFT JOIN order_items oi ON oi.order_id = o.id
    WHERE o.customer_id = p_customer
    GROUP BY o.id;
END $$;

SELECT * FROM customer_orders(1);
SELECT c.name, co.* FROM customers c, LATERAL customer_orders(c.id) co;

-- RETURN NEXT: build rows one at a time
CREATE OR REPLACE FUNCTION fib(n int) RETURNS SETOF int
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE a int := 0; b int := 1; t int;
BEGIN
    FOR i IN 1..n LOOP
        RETURN NEXT a;
        t := a + b; a := b; b := t;
    END LOOP;
END $$;
```

- `RETURN QUERY` / `RETURN NEXT` buffer the result in a tuplestore (spilling to disk if large) before the function returns.
- A column in `RETURNS TABLE` **shadows** a table column with the same name. Qualify with an alias, or use `#variable_conflict use_column`.
- Call set-returning functions in `FROM`, not in `SELECT` (cleaner semantics).

---

## 11\. Triggers and Trigger Functions

A trigger = **trigger function** (`RETURNS trigger`) + `CREATE TRIGGER` binding.

```sql
-- 1. Keep order total and stock in sync when items are inserted
CREATE OR REPLACE FUNCTION trg_order_items_after_ins() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE products SET stock = stock - NEW.qty            WHERE id = NEW.product_id;
    UPDATE orders   SET total = total + NEW.qty * NEW.unit_price WHERE id = NEW.order_id;
    RETURN NULL;                         -- ignored for AFTER row triggers
END $$;

CREATE TRIGGER order_items_after_ins
AFTER INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION trg_order_items_after_ins();

-- 2. Generic audit trigger, fires only when price really changes
CREATE OR REPLACE FUNCTION trg_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
    VALUES (TG_TABLE_NAME, TG_OP,
            COALESCE(NEW.id, OLD.id),
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;
END $$;

CREATE TRIGGER products_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION trg_audit();

-- 3. BEFORE trigger that rewrites the row
CREATE OR REPLACE FUNCTION trg_norm_email() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.email := lower(trim(NEW.email));
    RETURN NEW;                          -- returning NULL here would silently skip the row!
END $$;

CREATE TRIGGER customers_norm_email
BEFORE INSERT OR UPDATE OF email ON customers
FOR EACH ROW EXECUTE FUNCTION trg_norm_email();

-- Test
UPDATE products SET price = 99.90 WHERE id = 1;
SELECT table_name, action, old_data->>'price' AS old_price, new_data->>'price' AS new_price FROM audit_log;
```

Cheat sheet:

- **BEFORE** can modify `NEW` or cancel the row (`RETURN NULL`). **AFTER** sees committed-to-statement state; the return value is ignored.
- Special vars: `TG_OP`, `TG_TABLE_NAME`, `TG_WHEN`, `TG_ARGV`, `OLD`, `NEW`.
- Use `WHEN (...)` and `UPDATE OF col` to avoid firing for nothing. That is the cheapest filter you have.
- Prefer **statement-level triggers with transition tables** (`REFERENCING NEW TABLE AS nt`) for bulk operations, so one call instead of N.
- Triggers are invisible logic. Document them, and keep them idempotent and fast. Cascading triggers get painful to debug.

---

## 12\. Procedural vs Set-Based SQL

Same job, two ways: raise every price by 10%.

```sql
-- Row-by-row (a.k.a. "slow-by-slow")
DO $$
DECLARE r record;
BEGIN
    FOR r IN SELECT id FROM products LOOP
        UPDATE products SET price = price * 1.10 WHERE id = r.id;
    END LOOP;
END $$;

-- Set-based
UPDATE products SET price = price * 1.10;
```

To see the gap on realistic volume:

```sql
CREATE TEMP TABLE bench AS
SELECT g AS id, random()::numeric(6,2) AS v FROM generate_series(1, 500000) g;
ALTER TABLE bench ADD PRIMARY KEY (id);
\timing on

-- loop version
DO $$ DECLARE r record; BEGIN
  FOR r IN SELECT id FROM bench LOOP UPDATE bench SET v = v + 1 WHERE id = r.id; END LOOP;
END $$;

-- set-based version
UPDATE bench SET v = v + 1;
```

Expect the loop to be dramatically slower (typically orders of magnitude). Measure on your own hardware with `EXPLAIN (ANALYZE, BUFFERS)`.

**Why the loop loses**

- One statement execution per row (executor startup, snapshot, locking, WAL record) vs. one pass.
- The planner can't see the whole job: no hash/merge joins, no parallelism, no bulk optimizations.
- Per-row triggers, subtransactions (`EXCEPTION`), and round trips multiply the cost.
- Write amplification: many tiny updates create more dead tuples and more WAL than a single pass.

**When procedural is the right call**

- Row-dependent control flow that genuinely can't be expressed in SQL (state machines, per-row error handling, calling side-effects).
- Batching long jobs with intermediate `COMMIT` in procedures (keeps locks short, limits bloat and WAL bursts).
- Orchestration: dynamic DDL, migrations, multi-step admin tasks.
- Encapsulating logic close to the data to avoid network round trips.

**Rules of thumb**

1. Try pure SQL first: CTEs, `UPDATE ... FROM`, `INSERT ... SELECT`, window functions, `LATERAL`, `ON CONFLICT`.
2. If you must loop, loop over **batches**, not rows.
3. Mark functions `IMMUTABLE`/`STABLE`/`PARALLEL SAFE` correctly so the planner can optimize around them.
4. Prefer `LANGUAGE sql` for simple scalar/table functions: inlinable, no PL/pgSQL overhead.
5. Always verify with `EXPLAIN (ANALYZE, BUFFERS)`, and with `auto_explain` + `pg_stat_statements` in real workloads.

---

## Quick Reference

| Need | Use |
| --- | --- |
| Exact-one-row guard | `SELECT ... INTO STRICT` |
| Column-following types | `%TYPE`, `%ROWTYPE` |
| Row count after DML | `GET DIAGNOSTICS n = ROW_COUNT` |
| Re-throw error | `RAISE;` |
| Safe dynamic SQL | `format('%I')` + `USING` |
| Multi-row return | `RETURNS TABLE` + `RETURN QUERY` |
| Chunked commits | `PROCEDURE` + `COMMIT` |
| Cheap trigger filter | `WHEN (...)`, `UPDATE OF col` |
| Bulk trigger work | statement-level + transition tables |
