DO blocks for anonymous code in PostgreSQL - Step-by-Step Execution

Concept Flow - DO blocks for anonymous code

Start DO block

↓

Declare variables (optional)

↓

Execute procedural statements

↓

Handle exceptions (optional)

↓

End DO block

The DO block runs anonymous procedural code inside the database without creating a stored function. It executes statements step-by-step and ends.

Execution Sample

PostgreSQL

DO $$
BEGIN
  RAISE NOTICE 'Hello, world!';
END $$;

This DO block prints a message 'Hello, world!' as a notice when run.

Execution Table

Step	Action	Evaluation	Result
1	Enter DO block	Start execution	Ready to run statements
2	Execute RAISE NOTICE	Print message	NOTICE: Hello, world!
3	End DO block	No more statements	DO block finishes successfully

💡 All statements executed, DO block ends

Variable Tracker

Variable	Start	After 1	Final

Key Moments - 2 Insights

Why doesn't the DO block return a result set like a SELECT query?

The DO block runs procedural code for side effects like printing or updating data. It does not return rows, as shown in execution_table step 2 where it prints a notice instead of returning data.

Can I declare variables inside a DO block?

Yes, variables can be declared inside the DO block before procedural statements. This is optional and not shown in the simple example, but the concept_flow shows the declaration step before execution.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A Variables are declared

B The DO block ends

C A message is printed as a notice

D An error occurs

💡 Hint

Check the 'Action' and 'Result' columns in execution_table row 2

At which step does the DO block finish execution?

A Step 1

B Step 3

C Step 2

D There is no end step

💡 Hint

Look at the 'Step' and 'Result' columns in execution_table row 3

If you add variable declarations, where would they appear in the flow?

A Between start and executing statements

B After executing statements

C Before entering the DO block

D After the DO block ends

💡 Hint

Refer to the concept_flow diagram showing declaration before execution

Concept Snapshot

DO blocks run anonymous procedural code in PostgreSQL.
Syntax: DO $$ BEGIN statements; END $$;
Used for running code without creating functions.
Can declare variables and handle exceptions.
Does not return query results, only side effects.

Full Transcript

A DO block in PostgreSQL lets you run anonymous procedural code without creating a stored function. It starts by optionally declaring variables, then executes procedural statements like printing messages or updating data. The example shows a DO block printing 'Hello, world!' as a notice. The execution table traces entering the block, executing the print statement, and finishing the block. Variables can be declared inside the block but are optional. The DO block does not return rows like a SELECT query; it is for side effects. This visual helps beginners see each step the database takes when running a DO block.

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