DO blocks for anonymous code in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a DO block in PostgreSQL?

A DO block is a way to run anonymous procedural code in PostgreSQL without creating a stored function. It lets you write and execute code immediately.

beginner

How do you start a DO block in PostgreSQL?

You start a DO block with the keyword DO, followed by $$ to mark the start and end of the code, and specify the language, usually plpgsql.

intermediate

Can you declare variables inside a DO block?

Yes, inside a DO block you can declare variables in the DECLARE section before the BEGIN block, just like in a stored procedure.

beginner

What is the purpose of the BEGIN ... END section in a DO block?

The BEGIN ... END section contains the procedural code that runs when the DO block executes. It groups statements together.

intermediate

Give an example use case for a DO block.

You can use a DO block to perform quick data fixes, run procedural logic, or test code snippets without creating permanent functions.

What keyword starts a DO block in PostgreSQL?

A DO

B BEGIN

C EXECUTE

D RUN

The DO block always starts with the keyword DO.

Which language is commonly used inside a DO block?

A plpgsql

B SQL

C Python

D JavaScript

plpgsql is the procedural language used inside DO blocks in PostgreSQL.

Can you create permanent functions inside a DO block?

A Yes, DO blocks create functions

B Only if you use special syntax

C Yes, but only temporary functions

D No, DO blocks run anonymous code only

DO blocks run anonymous code and do not create permanent functions.

Where do you declare variables inside a DO block?

A Inside the BEGIN ... END section

B Before the DO keyword

C In the DECLARE section before BEGIN

D Variables are not allowed

Variables are declared in the DECLARE section before the BEGIN block.

What delimiter is used to mark the start and end of the code in a DO block?

A '' (single quotes)

B $$

C {}

D "" (double quotes)

The $$ delimiter marks the start and end of the code block in a DO statement.

Explain what a DO block is and how it is used in PostgreSQL.

Think about running quick code without saving it as a function.

Describe the structure of a DO block including variable declaration and code execution sections.

Consider the parts that make up the block from start to finish.

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