DO blocks for anonymous code in PostgreSQL - Deep Dive

Overview - DO blocks for anonymous code

What is it?

DO blocks in PostgreSQL let you run a piece of code without creating a permanent function. They are anonymous code blocks written in procedural languages like PL/pgSQL. You can use them to perform tasks such as complex calculations or conditional logic directly inside the database. This helps you run quick scripts or operations without saving them permanently.

Why it matters

Without DO blocks, you would have to create and store functions even for one-time or temporary tasks, cluttering your database with unused code. DO blocks let you test or run code snippets quickly and safely. This saves time and keeps your database clean, making it easier to manage and maintain.

Where it fits

Before learning DO blocks, you should understand basic SQL queries and the concept of functions in PostgreSQL. After mastering DO blocks, you can explore triggers, stored procedures, and advanced procedural programming inside the database.

Mental Model

Core Idea

A DO block is a temporary, anonymous container for procedural code that runs immediately without being saved.

Think of it like...

It's like writing a quick note on a sticky pad to solve a problem right now, instead of writing a full letter you keep forever.

┌─────────────────────────────┐
│        DO Block             │
│ ┌───────────────────────┐ │
│ │  Anonymous Code Block  │ │
│ │  (PL/pgSQL or others) │ │
│ └───────────────────────┘ │
│ Runs immediately, no save  │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Anonymous Code Blocks

Concept: Introduce the idea of running code without naming or saving it.

In PostgreSQL, a DO block lets you write code that runs once and disappears. You write it using the DO keyword, followed by the language and the code inside dollar signs. For example: DO $$ BEGIN RAISE NOTICE 'Hello, world!'; END $$ LANGUAGE plpgsql; This runs immediately and shows a message but does not create a function.

Result

The message 'Hello, world!' appears once, and no new function is stored in the database.

Understanding that DO blocks run code immediately without saving helps you quickly test or perform tasks without cluttering your database.

2

Foundation

Basic Syntax of DO Blocks

Concept: Learn the structure and required parts of a DO block.

A DO block starts with DO, then the code is wrapped inside dollar signs $$ or other delimiters. Inside, you write procedural code in a supported language like plpgsql. The block ends with a semicolon. Example: DO $$ BEGIN -- your code here END $$ LANGUAGE plpgsql;

Result

The database executes the code inside the block immediately when run.

Knowing the syntax is essential to write valid DO blocks that the database can execute without errors.

3

Intermediate

Using Variables and Control Structures

🤔Before reading on: do you think you can declare and use variables inside a DO block like in functions? Commit to your answer.

Concept: DO blocks support variables, loops, and conditional statements like functions do.

Inside a DO block, you can declare variables, use IF statements, loops, and other control structures. For example: DO $$ DECLARE counter INT := 0; BEGIN FOR counter IN 1..5 LOOP RAISE NOTICE 'Count: %', counter; END LOOP; END $$ LANGUAGE plpgsql;

Result

The database prints 'Count: 1' through 'Count: 5' messages sequentially.

Understanding that DO blocks can use full procedural logic lets you perform complex operations without creating permanent functions.

4

Intermediate

Performing Data Modifications Inside DO Blocks

🤔Before reading on: can DO blocks modify tables like regular SQL commands? Commit to your answer.

Concept: DO blocks can run SQL commands that change data, such as INSERT, UPDATE, or DELETE.

You can include SQL statements inside DO blocks to modify data. For example: DO $$ BEGIN UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'; END $$ LANGUAGE plpgsql;

Result

All employees in the Sales department get a 10% salary increase immediately.

Knowing that DO blocks can modify data allows you to run complex updates or maintenance tasks in a controlled, procedural way.

5

Intermediate

Exception Handling in DO Blocks

🤔Before reading on: do you think errors inside DO blocks stop the entire block or can be caught and handled? Commit to your answer.

Concept: DO blocks support exception handling to catch and manage errors during execution.

You can use BEGIN...EXCEPTION...END blocks inside DO to handle errors gracefully. For example: DO $$ BEGIN -- risky operation PERFORM some_function(); EXCEPTION WHEN others THEN RAISE NOTICE 'An error occurred but was handled'; END $$ LANGUAGE plpgsql;

Result

If some_function() causes an error, the block catches it and prints a notice instead of stopping abruptly.

Understanding exception handling in DO blocks helps you write robust code that can recover or report errors without crashing.

6

Advanced

Using DO Blocks for Dynamic SQL Execution

🤔Before reading on: can DO blocks execute SQL commands built as strings at runtime? Commit to your answer.

Concept: DO blocks can run dynamic SQL using EXECUTE to build and run commands on the fly.

You can create SQL statements as text and execute them inside DO blocks. For example: DO $$ DECLARE tbl_name TEXT := 'employees'; BEGIN EXECUTE format('UPDATE %I SET salary = salary * 1.05', tbl_name); END $$ LANGUAGE plpgsql;

Result

The employees table salaries increase by 5%, with the table name chosen dynamically.

Knowing how to run dynamic SQL inside DO blocks lets you write flexible, reusable code that adapts to different situations.

7

Expert

Performance and Transaction Behavior of DO Blocks

🤔Before reading on: do you think DO blocks run inside transactions or commit changes immediately? Commit to your answer.

Concept: DO blocks run inside the current transaction context and can be rolled back or committed with it.

When you run a DO block, it executes as part of the current transaction. If you run it inside a transaction block, all changes can be rolled back if needed. This means DO blocks do not auto-commit changes. For example: BEGIN; DO $$ BEGIN INSERT INTO logs(message) VALUES('Test'); END $$ LANGUAGE plpgsql; ROLLBACK; No new log entry remains after rollback.

Result

Changes made inside DO blocks follow the transaction rules of PostgreSQL, allowing safe testing and atomic operations.

Understanding transaction behavior of DO blocks prevents surprises in data consistency and helps manage complex workflows safely.

Under the Hood

When you run a DO block, PostgreSQL parses the block as an anonymous function, compiles it in memory, and executes it immediately. It does not store the function in the system catalogs. The procedural language handler processes the code, manages variables, control flow, and interacts with the SQL engine for queries and commands. The block runs within the current transaction context, so its changes are atomic and can be rolled back.

Why designed this way?

DO blocks were designed to allow quick, one-off procedural code execution without cluttering the database with permanent functions. This supports rapid development, testing, and maintenance tasks. Storing every small script as a function would bloat the system catalogs and complicate management. The design balances flexibility with cleanliness and safety.

┌───────────────┐
│   Client Run  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  DO Block SQL │
│  (Anonymous)  │
└──────┬────────┘
       │ Parsed & Compiled
       ▼
┌───────────────┐
│ Procedural    │
│ Language      │
│ Handler       │
└──────┬────────┘
       │ Executes Code
       ▼
┌───────────────┐
│  SQL Engine   │
│  (Queries,    │
│  Modifications)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Transaction   │
│ Context       │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a DO block create a permanent function in the database? Commit yes or no.

Common Belief: DO blocks create permanent functions that you can call later.

Reality: DO blocks run anonymous code immediately and do not create or save any function in the database.

Why it matters: Believing DO blocks create permanent functions can lead to confusion about code reuse and database clutter.

Quick: Can you call a DO block multiple times by name? Commit yes or no.

Common Belief: You can call a DO block repeatedly by referencing its name like a stored procedure.

Reality: DO blocks have no name and cannot be called again; they run once when executed.

Why it matters: Misunderstanding this leads to attempts to reuse DO blocks incorrectly instead of creating functions.

Quick: Do DO blocks auto-commit changes immediately? Commit yes or no.

Common Belief: Changes inside DO blocks are committed immediately and cannot be rolled back.

Reality: DO blocks run inside the current transaction and follow its commit or rollback behavior.

Why it matters: Assuming auto-commit can cause unexpected data states or loss of atomicity in complex operations.

Quick: Can you use any procedural language inside a DO block? Commit yes or no.

Common Belief: You can use any procedural language installed in PostgreSQL inside a DO block.

Reality: DO blocks support only procedural languages that allow anonymous code execution, like plpgsql, plpythonu, or plperl, but not all languages support DO blocks.

Why it matters: Trying unsupported languages causes errors and confusion about DO block capabilities.

Expert Zone

1

DO blocks share the same transaction context as the caller, so nested transactions or savepoints inside DO blocks require careful handling.

2

Because DO blocks are anonymous, debugging can be harder; using RAISE NOTICE and logging is essential for tracing execution.

3

DO blocks do not support input parameters, so passing data requires using variables or temporary tables, which affects design choices.

When NOT to use

DO blocks are not suitable when you need reusable, named functions or procedures. For repeated logic, create stored functions or procedures instead. Also, avoid DO blocks for complex business logic that requires version control or testing frameworks.

Production Patterns

In production, DO blocks are often used for one-time data migrations, maintenance scripts, or quick fixes. They are embedded in deployment scripts or admin tools to run procedural code without altering the database schema permanently.

Connections

Stored Procedures

DO blocks are like temporary, unnamed stored procedures that run once.

Understanding DO blocks clarifies the difference between one-time code execution and reusable database routines.

Transactions

DO blocks execute inside transactions, sharing commit and rollback behavior.

Knowing this connection helps manage data consistency and error recovery when running procedural code.

Scripting Languages

DO blocks embed procedural scripting inside SQL, blending declarative and imperative styles.

This shows how databases can act like programming environments, not just data stores.

Common Pitfalls

#1 Trying to reuse a DO block by calling it multiple times by name.

Wrong approach:

CALL my_do_block();

Correct approach:

Rewrite the code as a stored function or procedure with a name, then call it as needed.

Root cause:

Misunderstanding that DO blocks are anonymous and run only once when executed.

#2 Assuming changes inside DO blocks commit immediately and cannot be rolled back.

Wrong approach:

DO $$ BEGIN INSERT INTO table VALUES (1); END $$ LANGUAGE plpgsql; -- then expecting data to persist even after rollback

Correct approach:

Wrap DO block inside explicit transaction control:
BEGIN;
DO $$ BEGIN INSERT INTO table VALUES (1); END $$ LANGUAGE plpgsql;
ROLLBACK; -- data is not saved

Root cause:

Not realizing DO blocks run inside the current transaction context.

#3 Using unsupported procedural languages inside DO blocks.

Wrong approach:

DO $$ BEGIN PERFORM some_python_function(); END $$ LANGUAGE python;

Correct approach:

Use supported languages like plpgsql or plpythonu if installed, and verify language support for DO blocks.

Root cause:

Assuming all procedural languages installed support anonymous DO blocks.

Key Takeaways

DO blocks let you run procedural code immediately without saving it as a function.

They support variables, control flow, data modification, and exception handling like stored functions.

DO blocks run inside the current transaction, so their changes can be rolled back or committed with it.

They are ideal for one-time scripts, quick tests, or maintenance tasks without cluttering the database.

For reusable or complex logic, prefer stored functions or procedures instead of DO blocks.