Why Variable declaration and assignment in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could stop juggling numbers in your head and let the database do the math perfectly every time?

The Scenario

Imagine you need to calculate the total sales for a day by adding many numbers manually on paper or in a simple text file without any way to store intermediate results.

The Problem

Doing this by hand is slow and easy to make mistakes. You have to remember every number and every step, and if you lose track, you must start over. It's frustrating and wastes time.

The Solution

With variable declaration and assignment, you can store values in named containers inside your database code. This lets you keep track of numbers, update them easily, and use them later without confusion.

Before vs After

✗ Before

total = 0
for each sale:
  total = total + sale_amount
print total

✓ After

DECLARE total INTEGER := 0;
FOR sale IN SELECT * FROM sales LOOP
  total := total + sale.amount;
END LOOP;
RAISE NOTICE '%', total;

What It Enables

It makes complex calculations and data handling inside your database simple, clear, and error-free.

Real Life Example

A shop owner can calculate daily earnings by storing each sale amount in a variable and adding them up automatically, avoiding manual errors.

Key Takeaways

Variables store data temporarily for easy use.

They help keep calculations organized and accurate.

Using variables saves time and reduces mistakes.

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