PL/pgSQL Function Basics
Write PL/pgSQL functions with parameters, RETURNS TABLE, control flow (IF, LOOP, FOREACH), and exception handling.
PL/pgSQL Function Basics is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is PL/pgSQL?
PostgreSQL's built-in procedural language. SQL extended with variables, control flow, exceptions, and the ability to call queries dynamically. Used for stored procedures and trigger functions.
Function Skeleton
A simple function:
CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
SELECT add(2, 3); -- 5Variables
Declare with DECLARE; assign with := or INTO:
CREATE FUNCTION user_orders(uid BIGINT)
RETURNS INT AS $$
DECLARE
cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = uid;
RETURN cnt;
END;
$$ LANGUAGE plpgsql STABLE;Control Flow
IF / ELSE, FOR, WHILE, CASE:
IF cnt = 0 THEN
RAISE NOTICE 'no orders';
ELSIF cnt < 10 THEN
RAISE NOTICE 'few';
ELSE
RAISE NOTICE 'many';
END IF;
FOR i IN 1..10 LOOP
RAISE NOTICE '%', i;
END LOOP;Loop Over Query Results
Cursor-style iteration:
CREATE FUNCTION recalc_totals() RETURNS VOID AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id FROM users LOOP
UPDATE users SET
order_count = (SELECT COUNT(*) FROM orders WHERE user_id = r.id)
WHERE id = r.id;
END LOOP;
END;
$$ LANGUAGE plpgsql;RETURNS TABLE
Return a result set:
CREATE FUNCTION top_buyers(n INT)
RETURNS TABLE (user_id BIGINT, total NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT o.user_id, SUM(o.total)
FROM orders o
GROUP BY o.user_id
ORDER BY SUM(o.total) DESC
LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;
SELECT * FROM top_buyers(10);IN / OUT / INOUT Parameters
Multiple return values:
CREATE FUNCTION stats(uid BIGINT,
OUT total NUMERIC,
OUT cnt INT) AS $$
BEGIN
SELECT SUM(total), COUNT(*) INTO total, cnt
FROM orders WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql STABLE;
SELECT * FROM stats(42); -- total | cntException Handling
BEGIN ... EXCEPTION WHEN ... THEN:
BEGIN
INSERT INTO users (email) VALUES ($1);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'email already exists';
WHEN OTHERS THEN
RAISE;
END;RAISE for Logging
Levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION:
RAISE NOTICE 'processed % rows', cnt;
RAISE EXCEPTION 'bad state: %', state USING ERRCODE = 'check_violation';Volatility Markers
Mark functions correctly for the planner:
- IMMUTABLE — same args always → same result (no I/O); allows inlining and indexable use
- STABLE — same args → same result within a query
- VOLATILE — may differ per call (default; correct for NOW(), RANDOM())
SECURITY DEFINER
Runs with the function owner's privileges — use carefully:
CREATE FUNCTION sensitive_op() RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$ ... $$;
-- Always set search_path explicitly to avoid hijacking:
ALTER FUNCTION sensitive_op() SET search_path = public, pg_temp;Recap
PL/pgSQL extends SQL with loops, IF, exceptions, and structured returns. Use for cohesive multi-step logic that benefits from running close to the data.
Quick Check
Which volatility marker should you use for a function that returns the same result given the same args, with no side effects or I/O?
Frequently asked questions
Is the “PL/pgSQL Function Basics” lesson free?
Yes — the full text of “PL/pgSQL Function Basics” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “PL/pgSQL Function Basics”?
Write PL/pgSQL functions with parameters, RETURNS TABLE, control flow (IF, LOOP, FOREACH), and exception handling. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “PL/pgSQL Function Basics” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.