Variable declaration and assignment in PostgreSQL - Step-by-Step Execution

Concept Flow - Variable declaration and assignment

Start

↓

Declare variable with type

↓

Assign value to variable

↓

Use variable in expressions or queries

↓

End or reassign variable

↓

Finish

This flow shows how you declare a variable with a type, assign a value, use it, and optionally reassign it before finishing.

Execution Sample

PostgreSQL

DO $$
DECLARE
  my_var integer;
BEGIN
  my_var := 10;
END $$;

This code declares an integer variable 'my_var' and assigns it the value 10 inside a DO block.

Execution Table

Step	Action	Variable	Value	Notes
1	Declare variable 'my_var' as integer	my_var	NULL	Variable declared but not assigned yet
2	Assign 10 to 'my_var'	my_var	10	Variable now holds value 10
3	End of block	my_var	10	Variable retains value until block ends

💡 Block ends, variable scope ends, 'my_var' no longer accessible

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
my_var	undefined	NULL	10	10

Key Moments - 3 Insights

Why is the variable value NULL right after declaration?

Because declaring a variable reserves space but does not assign a value yet, so it defaults to NULL as shown in step 1 of the execution_table.

Can you use the variable before assigning a value?

No, using the variable before assignment will result in NULL or error depending on context, as the variable holds NULL after declaration (step 1).

What happens to the variable after the block ends?

The variable goes out of scope and is no longer accessible, as noted in the exit_note after step 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'my_var' immediately after declaration?

A NULL

B 0

C 10

D undefined

💡 Hint

Check the 'Value' column in row 1 of the execution_table.

At which step does 'my_var' get assigned the value 10?

A Step 1

B Step 3

C Step 2

D It never gets assigned

💡 Hint

Look at the 'Action' and 'Value' columns in the execution_table.

If you tried to use 'my_var' after the block ends, what would happen?

A It resets to NULL

B It is not accessible (out of scope)

C It keeps the last value 10

D It throws a syntax error

💡 Hint

Refer to the exit_note about variable scope after step 3.

Concept Snapshot

Variable declaration in PostgreSQL uses DECLARE with a type.
Assignment uses := operator inside a block.
Variables start as NULL until assigned.
Scope is limited to the block.
Use variables after assignment for expressions.

Full Transcript

In PostgreSQL, you declare variables inside a block using DECLARE and specify the type. Initially, variables have NULL value until you assign them using :=. The example shows declaring 'my_var' as integer, then assigning 10. The variable keeps this value until the block ends, after which it is no longer accessible. Beginners often wonder why the variable is NULL after declaration and that you cannot use it before assignment. Also, variables only exist inside the block where declared.

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