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.