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.