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.