DO Blocks and Anonymous Code
Run ad-hoc PL/pgSQL with DO blocks, manage dollar-quoting, and use DO blocks for one-off migrations.
DO Blocks and Anonymous Code is a free SQL Academy lesson on CoddyKit — lesson 3 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 a DO Block?
A DO block lets you run PL/pgSQL without defining a named function. Perfect for one-off migrations and ad-hoc scripts.
Basic DO Block
Minimal syntax:
DO $$
BEGIN
RAISE NOTICE 'Hello from PL/pgSQL';
END;
$$ LANGUAGE plpgsql;
-- The language defaults to plpgsql, so you can omit it:
DO $$ BEGIN RAISE NOTICE 'Hi'; END $$;Dollar-Quoting
The $$ delimiter avoids escaping single quotes. You can use a tag for nested code:
DO $outer$
BEGIN
EXECUTE $inner$ SELECT 'embedded \'quotes\'' $inner$;
END;
$outer$;Conditional DDL
Add a column only if it doesn't exist:
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name='users' AND column_name='last_login'
) THEN
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
END IF;
END $$;Loops for Bulk Migration
Batch update a huge table:
DO $$
DECLARE
rows_updated INT;
BEGIN
LOOP
UPDATE users SET tier = compute_tier(...)
WHERE id IN (
SELECT id FROM users WHERE tier IS NULL LIMIT 10000
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
COMMIT;
END LOOP;
END $$;Dynamic SQL with EXECUTE
Build SQL strings safely with format() and EXECUTE:
DO $$
DECLARE
tbl TEXT;
BEGIN
FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname='public' LOOP
EXECUTE format('ANALYZE %I;', tbl);
END LOOP;
END $$;Transaction Control Inside DO
Older PG: a DO block runs inside a single implicit transaction. PG 11+ allows COMMIT/ROLLBACK inside procedures (CALL) but not in DO. For batched migrations with commits, use a procedure.
Procedures vs DO
- DO — anonymous, run once, in current transaction
- PROCEDURE — named, supports COMMIT/ROLLBACK, called via CALL
CREATE PROCEDURE migrate_users() LANGUAGE plpgsql AS $$
BEGIN
-- can COMMIT here
END $$;
CALL migrate_users();Error Handling
EXCEPTION blocks work the same as in named functions:
DO $$
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'failed: %', SQLERRM;
END $$;Don't Use DO for Repeated Tasks
If you'll run the same logic more than once, define a function or procedure. DO is for one-offs.
Use DO in Migrations
Most migration tools (Flyway, Sqitch) run plain SQL. DO blocks let you embed control flow without separate functions.
Recap
DO blocks are ad-hoc PL/pgSQL.
- Single anonymous run
- Great for conditional DDL and migrations
- Watch dollar-quoting
- For transaction control, use PROCEDURE
Quick Check
You want to run PL/pgSQL once during a migration without creating a named function. Which construct?
Frequently asked questions
Is the “DO Blocks and Anonymous Code” lesson free?
Yes — the full text of “DO Blocks and Anonymous Code” 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 “DO Blocks and Anonymous Code”?
Run ad-hoc PL/pgSQL with DO blocks, manage dollar-quoting, and use DO blocks for one-off migrations. 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 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “DO Blocks and Anonymous Code” 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.
All lessons in this course
- Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
- PL/pgSQL Function Basics
- DO Blocks and Anonymous Code
- Auditing Tables with Triggers