Skip to main content

PL/pgSQL for People Who Already Know SQL

Updated 15 min readMarkdown
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)

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

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

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

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

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

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

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
-- 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

-- 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

-- 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

-- 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

-- 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

-- 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.

-- 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%.

-- 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:

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

No comments yet. Be the first to comment.