Variable declaration and assignment in PostgreSQL - Mini Project: Build & Apply

Variable Declaration and Assignment in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database and want to store some temporary values inside a block of code to use later. This is like keeping notes on a sticky note while you work.

🎯 Goal: Create a simple PostgreSQL anonymous code block where you declare a variable called counter, assign it a starting value, then update it by adding a number.

📋 What You'll Learn

Declare a variable named counter of type integer

Assign the value 10 to counter

Add 5 to counter using an assignment

Use a PostgreSQL anonymous code block with DO and BEGIN ... END

💡 Why This Matters

🌍 Real World

Database developers often need to store temporary values during complex operations or scripts. Variables help keep track of these values inside procedural code.

💼 Career

Knowing how to declare and assign variables in PostgreSQL is essential for writing stored procedures, triggers, and scripts that automate database tasks.

1

Declare the variable

Write a PostgreSQL anonymous code block starting with DO $$ and BEGIN. Inside it, declare a variable called counter of type integer. End the block with END $$;.

PostgreSQL

DO $$
BEGIN
    -- Declare the variable counter here
END $$;

Need a hint?

Use the DECLARE section inside the DO $$ ... END $$; block to declare variables.

2

Assign an initial value

Inside the BEGIN block, assign the value 10 to the variable counter using the := operator.

PostgreSQL

DO $$
DECLARE
    counter integer;
BEGIN
    -- Assign 10 to counter here
END $$;

Need a hint?

Use counter := 10; to assign the value inside the BEGIN block.

3

Update the variable value

Add 5 to the current value of counter by updating it with counter := counter + 5; inside the BEGIN block after the initial assignment.

PostgreSQL

DO $$
DECLARE
    counter integer;
BEGIN
    counter := 10;
    -- Add 5 to counter here
END $$;

Need a hint?

Use counter := counter + 5; to add 5 to the existing value.

4

Complete the block

Ensure the entire PostgreSQL anonymous block is correctly structured with DO $$, DECLARE, BEGIN, the variable declaration, assignments, and ends with END $$;.

PostgreSQL

DO $$
DECLARE
    counter integer;
BEGIN
    counter := 10;
    counter := counter + 5;
    -- Finalize the block
END $$;

Need a hint?

Check that all parts of the block are present and in the right order.