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 ... INTOtakes the first row silently. UseINTO STRICTto 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
EXCEPTIONblock 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 withplpgsql.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
OUTparam return a scalar, multipleOUTparams return a record. VARIADICandDEFAULTparams 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 $$;
recordhas no structure until assigned. Accessing fields before that throws.- Every table automatically defines a composite type of the same name, so
RETURNS SETOF productsworks. 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, andWITH HOLDacross 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 NEXTbuffer the result in a tuplestore (spilling to disk if large) before the function returns.- A column in
RETURNS TABLEshadows a table column with the same name. Qualify with an alias, or use#variable_conflict use_column. - Call set-returning functions in
FROM, not inSELECT(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
NEWor 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 (...)andUPDATE OF colto 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
COMMITin 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
- Try pure SQL first: CTEs,
UPDATE ... FROM,INSERT ... SELECT, window functions,LATERAL,ON CONFLICT. - If you must loop, loop over batches, not rows.
- Mark functions
IMMUTABLE/STABLE/PARALLEL SAFEcorrectly so the planner can optimize around them. - Prefer
LANGUAGE sqlfor simple scalar/table functions: inlinable, no PL/pgSQL overhead. - Always verify with
EXPLAIN (ANALYZE, BUFFERS), and withauto_explain+pg_stat_statementsin 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 |
Comments
Join the discussionNo comments yet. Be the first to comment.



