DO blocks for anonymous code in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple DO block with RAISE NOTICE

What will be the output message when this DO block runs in PostgreSQL?

PostgreSQL

DO $$
BEGIN
  RAISE NOTICE 'Hello from DO block!';
END $$;

A WARNING: Hello from DO block!

B ERROR: syntax error near 'RAISE'

C No output, the block runs silently

D NOTICE: Hello from DO block!

💡 Hint

RAISE NOTICE prints a message to the client.

The RAISE NOTICE command outputs a notice message to the client. Here it prints 'Hello from DO block!'.

📝 Syntax

intermediate

Identify the syntax error in this DO block

Which option correctly identifies the syntax error in this DO block?

PostgreSQL

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

A Missing semicolon after PERFORM statement

B Division by zero error at runtime, no syntax error

C Incorrect DO block delimiter, should use single quotes

D BEGIN block must be followed by DECLARE section

💡 Hint

Check if the code syntax is valid before runtime errors.

The code syntax is valid. The error occurs at runtime due to division by zero, not a syntax error.

❓ optimization

advanced

Best way to loop 5 times in a DO block

Which DO block code efficiently loops exactly 5 times and raises a notice each iteration?

A

DO $$
BEGIN
  FOR i IN 1..5 LOOP
    RAISE NOTICE 'Iteration %', i;
  END LOOP;
END $$;

B

DO $$
DECLARE i INT := 1;
BEGIN
  WHILE i <= 5 LOOP
    RAISE NOTICE 'Iteration %', i;
    i := i + 1;
  END LOOP;
END $$;

C

DO $$
BEGIN
  FOR i IN ARRAY[1,2,3,4,5] LOOP
    RAISE NOTICE 'Iteration %', i;
  END LOOP;
END $$;

D

DO $$
DECLARE i INT;
BEGIN
  FOR i IN 1..5 LOOP
    RAISE NOTICE 'Iteration %', i;
  END LOOP;
END $$;

💡 Hint

PostgreSQL supports FOR loops with range syntax 1..5.

Option A uses the correct FOR loop syntax with 1..5 range. Option A uses a WHILE loop but is more verbose. Option A loops over an array which works but is less efficient. Option A uses invalid FOR loop syntax '1 TO 5'.

🔧 Debug

advanced

Why does this DO block fail to compile?

This DO block fails with a syntax error. What is the cause?

PostgreSQL

DO $$
BEGIN
  RAISE NOTICE 'Value: %', my_var;
END $$;

A Missing semicolon after BEGIN

B RAISE NOTICE requires double quotes around the message

C Variable my_var is not declared before use

D DO blocks cannot use RAISE NOTICE

💡 Hint

Variables must be declared in DECLARE section before use.

The variable my_var is used without declaration, causing a compilation error.

🧠 Conceptual

expert

Effect of transaction control commands inside DO blocks

Which statement about transaction control commands inside DO blocks is true?

A DO blocks run inside a single transaction and cannot contain COMMIT or ROLLBACK

B You can use COMMIT and ROLLBACK inside DO blocks to control transactions

C DO blocks automatically commit after each statement inside them

D DO blocks run outside transactions and thus ignore COMMIT and ROLLBACK

💡 Hint

Consider how PostgreSQL treats anonymous code blocks and transactions.

DO blocks run inside a single transaction context. You cannot use COMMIT or ROLLBACK inside them; attempting to do so causes an error.

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