Variable declaration and assignment in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this variable assignment in a PostgreSQL function?

Consider the following PL/pgSQL block. What will be the value of result after execution?

PostgreSQL

DO $$
DECLARE
  result integer := 5;
BEGIN
  result := result + 10;
  RAISE NOTICE 'Result is %', result;
END $$;

A Syntax error due to variable initialization

B Result is 15

C Result is 10

D Result is 5

💡 Hint

Remember that variables declared in PL/pgSQL can be initialized and then reassigned.

The variable result is initialized to 5, then 10 is added, making it 15. The RAISE NOTICE prints this value.

📝 Syntax

intermediate

Which option correctly declares and assigns a variable in PL/pgSQL?

Choose the correct syntax to declare an integer variable count and assign it the value 100.

A DECLARE count integer = 100;

B DECLARE count := integer 100;

C DECLARE count integer; count = 100;

D count integer := 100;

💡 Hint

PL/pgSQL uses := for assignment during declaration.

In PL/pgSQL, variables are declared with their type and assigned using :=. Option D follows this syntax.

🔧 Debug

advanced

Why does this PL/pgSQL block raise an error?

Examine the code below and identify the cause of the error.

PostgreSQL

DO $$
DECLARE
  total integer;
BEGIN
  total := 'ten';
  RAISE NOTICE 'Total is %', total;
END $$;

A Type mismatch error because 'ten' is a string, not an integer

B Syntax error due to missing semicolon after declaration

C Runtime error because variable total is not initialized

D No error, the code runs and prints 'Total is ten'

💡 Hint

Check the data type of the assigned value compared to the variable type.

The variable total is declared as integer but assigned a string 'ten', causing a type mismatch error.

🧠 Conceptual

advanced

What happens if you declare a variable without initializing it in PL/pgSQL?

Consider the variable declaration DECLARE count integer; without assignment. What is the initial value of count?

A It contains random garbage value

B It causes a syntax error

C It is NULL by default

D It is zero by default

💡 Hint

Think about how SQL handles uninitialized variables or columns.

In PL/pgSQL, uninitialized variables default to NULL, meaning no value is assigned yet.

❓ optimization

expert

Which variable declaration is most efficient for repeated integer assignment in PL/pgSQL?

You need a variable counter to increment many times inside a loop. Which declaration is best for performance and clarity?

A DECLARE counter integer := 0;

B DECLARE counter integer;

C DECLARE counter bigint := 0;

D DECLARE counter text := '0';

💡 Hint

Consider type size and initialization for numeric counters.

Declaring counter as integer and initializing to 0 is efficient and clear for counting loops.

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