Variable declaration and assignment in PostgreSQL

Introduction

Variables let you store and reuse values inside your database code. This helps you keep things organized and avoid repeating yourself.

When you want to save a value to use multiple times in a query or function.

When you need to perform calculations step-by-step and keep intermediate results.

When writing stored procedures or functions that require temporary data storage.

When you want to make your SQL code easier to read by naming important values.

When you want to change a value during a process without affecting the original data.

Syntax

PostgreSQL

DECLARE variable_name data_type [DEFAULT initial_value];

-- To assign a value later inside a block:
variable_name := new_value;

Variables are declared inside PL/pgSQL blocks like functions or DO blocks.

Use := to assign or change the value of a variable after declaration.

Examples

This declares a variable named count as an integer and sets it to 0 initially.

PostgreSQL

DECLARE
  count INTEGER DEFAULT 0;

Declares a text variable name and assigns 'Alice' to it inside the block.

PostgreSQL

DECLARE
  name TEXT;
BEGIN
  name := 'Alice';
END;

Declares a numeric variable total and assigns a decimal value.

PostgreSQL

DECLARE
  total NUMERIC;
BEGIN
  total := 100.50;
END;

Sample Program

This code declares two variables, assigns a value to name, and prints a greeting message combining both variables.

PostgreSQL

DO $$
DECLARE
  greeting TEXT DEFAULT 'Hello';
  name TEXT;
BEGIN
  name := 'Bob';
  RAISE NOTICE '% %!', greeting, name;
END $$;

Important Notes

Variables only exist inside the block where they are declared.

You cannot use variables directly in plain SQL; they work inside PL/pgSQL blocks.

Use RAISE NOTICE to print variable values for debugging.

Summary

Variables store temporary values inside PL/pgSQL blocks.

Declare variables with DECLARE and assign values with :=.

Use variables to make your database code clearer and reusable.

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