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 $$;

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 $$;

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 $$;

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 $$;

Hint

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

Practice

1. What is the correct way to declare a variable named counter of type integer in a PL/pgSQL block?

easy

A. DECLARE counter integer;

B. counter integer;

C. DECLARE counter integer = 0;

D. counter := integer;

Solution

1. Step 1: Understand variable declaration syntax

   In PL/pgSQL, variables are declared inside a DECLARE section without repeating the DECLARE keyword for each variable. The correct syntax is: variable_name data_type;

2. Step 2: Identify the correct declaration

   The line counter integer; correctly declares the variable inside the DECLARE block. Including the DECLARE keyword before each variable is incorrect.

3. Final Answer:

   counter integer; -> Option B

4. Quick Check:

   Declare variables inside DECLARE block without repeating DECLARE [OK]

Hint: Declare variables inside DECLARE block with type only [OK]

Common Mistakes:

• Omitting DECLARE keyword
• Assigning value during declaration without :=
• Using := in declaration line

2. Which of the following is the correct syntax to assign the value 10 to a variable count after it has been declared in PL/pgSQL?

easy

A. count = 10;

B. count == 10;

C. SET count = 10;

D. count := 10;

Solution

1. Step 1: Recall assignment operator in PL/pgSQL

   PL/pgSQL uses := to assign values to variables, not = or ==.

2. Step 2: Identify correct assignment syntax

   count := 10; uses count := 10; which is the correct way to assign a value.

3. Final Answer:

   count := 10; -> Option D

4. Quick Check:

   Use := for assignment in PL/pgSQL [OK]

Hint: Use := to assign values to variables [OK]

Common Mistakes:

• Using = instead of :=
• Using SET keyword incorrectly
• Using == like in other languages

3. Consider the following PL/pgSQL block:

DECLARE
  total integer := 5;
BEGIN
  total := total + 3;
  RAISE NOTICE '%', total;
END;

What will be the output when this block runs?

medium

A. 8

B. 5

C. 3

D. Error: variable not initialized

Solution

1. Step 1: Analyze initial value assignment

   The variable total is declared and initialized to 5.

2. Step 2: Calculate the new value after addition

   The statement total := total + 3; adds 3 to 5, resulting in 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add assigned values step-by-step to find final result [OK]

Common Mistakes:

• Ignoring initial value and assuming zero
• Confusing assignment operator with equality
• Expecting error due to missing BEGIN

4. Identify the error in the following PL/pgSQL code snippet:

DECLARE
  name text;
BEGIN
  name = 'Alice';
END;

medium

A. Incorrect assignment operator used

B. Missing DECLARE keyword

C. Variable name not declared

D. Missing semicolon after BEGIN

Solution

1. Step 1: Check variable declaration

   The variable name is declared correctly with type text.

2. Step 2: Check assignment syntax

   The assignment uses = which is incorrect in PL/pgSQL; it should use :=.

3. Final Answer:

   Incorrect assignment operator used -> Option A

4. Quick Check:

   Use := for assignment, not = [OK]

Hint: Use := for assignment, not = [OK]

Common Mistakes:

• Using = instead of :=
• Forgetting semicolon after assignment
• Confusing declaration and assignment syntax

5. You want to declare two variables a and b as integers, assign a the value 4, and then assign b the value of a multiplied by 3. Which of the following PL/pgSQL code snippets correctly does this?

hard

A. DECLARE
b integer := a * 3;
a integer := 4;
BEGIN
NULL;
END;

B. DECLARE
a integer;
b integer;
BEGIN
a = 4;
b = a * 3;
END;

C. DECLARE
a integer;
b integer;
BEGIN
a := 4;
b := a * 3;
END;

D. DECLARE
a integer := 4;
b integer;
BEGIN
b = a * 3;
END;

Solution

1. Step 1: Check variable declaration and initialization

   DECLARE a integer; b integer; BEGIN a := 4; b := a * 3; END; declares both variables without initial values, then assigns values inside the BEGIN block using correct assignment operator :=.

2. Step 2: Verify assignment and calculation

   It assigns a := 4; and then b := a * 3;, which correctly sets b to 12.

3. Final Answer:

   correctly declares and assigns variables with := -> Option C

4. Quick Check:

   Declare first, assign with := inside BEGIN [OK]

Hint: Declare variables first, assign values inside BEGIN with := [OK]

Common Mistakes:

• Using = instead of :=
• Assigning values during declaration with expressions
• Missing BEGIN block for assignments