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.

Practice

1. What is the main purpose of a DO block in PostgreSQL?

easy

A. To define a new permanent function

B. To create a new table in the database

C. To execute a SELECT query and return results

D. To run anonymous procedural code immediately without creating a permanent function

Solution

1. Step 1: Understand the role of DO blocks

   DO blocks allow running procedural code immediately without saving it as a function.

2. Step 2: Compare with other options

   Creating tables or functions is done with other commands, and DO blocks do not return query results.

3. Final Answer:

   To run anonymous procedural code immediately without creating a permanent function -> Option D

4. Quick Check:

   DO blocks = anonymous immediate code execution [OK]

Hint: DO blocks run code immediately without saving functions [OK]

Common Mistakes:

• Thinking DO blocks create permanent functions
• Confusing DO blocks with SELECT queries
• Assuming DO blocks create tables

2. Which of the following is the correct syntax to start a DO block in PostgreSQL?

easy

A. DO $$ BEGIN END $$ LANGUAGE plpgsql;

B. DO LANGUAGE plpgsql BEGIN END;

C. DO BEGIN $$ END LANGUAGE plpgsql;

D. DO $$ LANGUAGE plpgsql BEGIN END $$;

Solution

1. Step 1: Recall the correct DO block syntax

   The DO block uses dollar quoting $$ to enclose the code, with LANGUAGE plpgsql specified after the block.

2. Step 2: Check each option

   DO $$ BEGIN END $$ LANGUAGE plpgsql; correctly places $$ around BEGIN...END and specifies LANGUAGE plpgsql after the block.

3. Final Answer:

   DO $$ BEGIN END $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   DO block syntax = DO $$ code $$ LANGUAGE plpgsql; [OK]

Hint: Use DO $$ ... $$ LANGUAGE plpgsql; to start DO blocks [OK]

Common Mistakes:

• Placing LANGUAGE plpgsql before BEGIN
• Not using dollar quoting $$
• Misordering keywords in the DO block

3. What will be the output of this DO block?

DO $$
BEGIN
  RAISE NOTICE 'Hello, PostgreSQL!';
END
$$ LANGUAGE plpgsql;

medium

A. It prints 'Hello, PostgreSQL!' as a notice message

B. It returns a result set with 'Hello, PostgreSQL!'

C. It causes a syntax error

D. It creates a permanent function named 'Hello, PostgreSQL!'

Solution

1. Step 1: Understand RAISE NOTICE in DO blocks

   RAISE NOTICE outputs a message to the client as an informational notice, not a query result.

2. Step 2: Analyze the DO block behavior

   The block runs immediately and prints the notice message but does not return rows or create functions.

3. Final Answer:

   It prints 'Hello, PostgreSQL!' as a notice message -> Option A

4. Quick Check:

   RAISE NOTICE outputs messages, not query results [OK]

Hint: RAISE NOTICE shows messages, no query output [OK]

Common Mistakes:

• Expecting query result rows
• Thinking it creates a function
• Confusing notice with error

4. Identify the error in this DO block:

DO $$
BEGIN
  PERFORM 1/0;
END
$$ LANGUAGE plpgsql;

medium

A. Syntax error due to missing semicolon

B. Division by zero runtime error

C. Missing LANGUAGE specification

D. Invalid use of PERFORM keyword

Solution

1. Step 1: Analyze the code inside DO block

   The statement PERFORM 1/0 attempts to divide 1 by zero, which is not allowed.

2. Step 2: Identify the error type

   This causes a runtime error (division by zero), not a syntax error or missing keyword.

3. Final Answer:

   Division by zero runtime error -> Option B

4. Quick Check:

   1/0 causes runtime error, not syntax [OK]

Hint: Check for runtime errors like division by zero [OK]

Common Mistakes:

• Confusing runtime error with syntax error
• Ignoring division by zero possibility
• Assuming PERFORM is invalid here

5. You want to update a table users to set active = false for all users who haven't logged in for over a year. Which DO block correctly performs this task?

hard

A. DO $$ BEGIN UPDATE users SET active = false WHERE last_login > NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

B. DO $$ BEGIN SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

C. DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

D. DO $$ UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check the DO block structure and logic

   DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; correctly uses BEGIN...END with an UPDATE statement and the right condition for last_login older than 1 year.

2. Step 2: Verify other options

   DO $$ BEGIN SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; only selects rows, no update. DO $$ UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; misses BEGIN...END block. DO $$ BEGIN UPDATE users SET active = false WHERE last_login > NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; uses wrong condition (greater than instead of less than).

3. Final Answer:

   DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   Correct DO block with UPDATE and condition [OK]

Hint: Use BEGIN...END with UPDATE and correct WHERE condition [OK]

Common Mistakes:

• Omitting BEGIN...END block
• Using wrong comparison operator in WHERE
• Using SELECT instead of UPDATE