DO blocks for anonymous code in PostgreSQL - Mini Project: Build & Apply

Using DO Blocks for Anonymous Code in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to perform some quick checks and updates without creating permanent functions. PostgreSQL's DO blocks let you run anonymous code blocks for such tasks.

🎯 Goal: Build a DO block in PostgreSQL that declares a variable, performs a simple calculation, and raises a notice with the result.

📋 What You'll Learn

Create a DO block using PL/pgSQL language

Declare a variable named total_books and set it to 150

Declare a variable named sold_books and set it to 45

Calculate the remaining books by subtracting sold_books from total_books

Raise a notice showing the remaining books

💡 Why This Matters

🌍 Real World

DO blocks are useful for quick database tasks like data validation, updates, or calculations without creating permanent functions.

💼 Career

Database developers and administrators often use DO blocks to test code snippets or perform maintenance tasks efficiently.

1

Create the DO block skeleton

Write a DO block in PostgreSQL using plpgsql language with an empty BEGIN-END section.

PostgreSQL

DO $$
BEGIN
    -- Your code here
END $$ LANGUAGE plpgsql;

Hint

Start with DO $$ BEGIN END $$ LANGUAGE plpgsql; to create an anonymous code block.

2

Declare variables inside the DO block

Inside the DO block, declare two integer variables: total_books set to 150 and sold_books set to 45. Use the DECLARE section.

PostgreSQL

DO $$
DECLARE
    -- Declare variables here
BEGIN
END $$ LANGUAGE plpgsql;

Hint

Use DECLARE before BEGIN to declare variables with initial values.

3

Calculate remaining books

In the DECLARE section, declare a new variable remaining_books and set it to total_books - sold_books.

PostgreSQL

DO $$
DECLARE
    total_books integer := 150;
    sold_books integer := 45;
    -- Declare remaining_books here
BEGIN
END $$ LANGUAGE plpgsql;

Hint

Declare remaining_books in the DECLARE section and assign the difference.

4

Raise a notice with the remaining books

Inside the BEGIN section, add a RAISE NOTICE statement that outputs the text 'Remaining books: ' followed by the value of remaining_books.

PostgreSQL

DO $$
DECLARE
    total_books integer := 150;
    sold_books integer := 45;
    remaining_books integer := total_books - sold_books;
BEGIN
    -- Raise notice here
END $$ LANGUAGE plpgsql;

Hint

Use RAISE NOTICE 'Remaining books: %', remaining_books; to display the value.

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