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;

Need a 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;

Need a 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;

Need a 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;

Need a hint?

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