FOREACH for array iteration in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FOREACH loop with integer array

What will be the output of the following PL/pgSQL block?

DO $$
DECLARE
  nums integer[] := ARRAY[1, 2, 3];
  total integer := 0;
  num integer;
BEGIN
  FOREACH num IN ARRAY nums LOOP
    total := total + num;
  END LOOP;
  RAISE NOTICE 'Total: %', total;
END $$;

PostgreSQL

DO $$
DECLARE
  nums integer[] := ARRAY[1, 2, 3];
  total integer := 0;
  num integer;
BEGIN
  FOREACH num IN ARRAY nums LOOP
    total := total + num;
  END LOOP;
  RAISE NOTICE 'Total: %', total;
END $$;

A Total: 3

B Total: 0

C Total: 6

D Syntax error: missing variable declaration

💡 Hint

Think about how FOREACH iterates over each element and adds it to total.

The FOREACH loop iterates over each element in the array nums (1, 2, 3) and adds each to total. So total becomes 1+2+3 = 6.

📝 Syntax

intermediate

Identify the syntax error in FOREACH usage

Which option contains a syntax error in using FOREACH to iterate over a text array in PL/pgSQL?

PostgreSQL

DECLARE
  fruits text[] := ARRAY['apple', 'banana', 'cherry'];
  fruit text;
BEGIN
  FOREACH fruit IN fruits LOOP
    RAISE NOTICE '%', fruit;
  END LOOP;
END;

A FOREACH fruit IN fruits LOOP

B FOREACH fruit IN fruit LOOP

C FOREACH fruit IN ARRAY fruits LOOP

D POOL stiurf YARRA NI tiurf HCAEROF

💡 Hint

Check the correct syntax for iterating over arrays with FOREACH.

FOREACH requires the keyword ARRAY before the array variable to iterate properly. Option A misses ARRAY, causing a syntax error.

❓ optimization

advanced

Optimizing FOREACH loop for large arrays

You have a large integer array and want to sum its elements efficiently in PL/pgSQL. Which approach is the most efficient?

A Use FOREACH loop to add each element to a total variable

B Use unnest() in a SELECT SUM() query on the array

C Convert array to string and parse numbers manually

D Use FOREACH loop with RAISE NOTICE for each element

💡 Hint

Consider built-in SQL functions for aggregation over arrays.

Using unnest() with SELECT SUM() leverages SQL set operations which are faster than looping in PL/pgSQL.

🔧 Debug

advanced

Debugging FOREACH loop with NULL elements

Given the array ARRAY[1, NULL, 3], what will be the value of total after this PL/pgSQL block?

DECLARE
  nums integer[] := ARRAY[1, NULL, 3];
  total integer := 0;
  num integer;
BEGIN
  FOREACH num IN ARRAY nums LOOP
    total := total + num;
  END LOOP;
  RAISE NOTICE 'Total: %', total;
END;

A Total: 4

B Runtime error: cannot add NULL

C Total: 0

D Total: NULL

💡 Hint

Think about how NULL values affect arithmetic in SQL.

Adding NULL to any number results in NULL in SQL, so total becomes NULL after adding the NULL element.

🧠 Conceptual

expert

Understanding FOREACH loop variable scope

In PL/pgSQL, what is the scope of the loop variable declared in a FOREACH loop iterating over an array?

A The loop variable must be declared outside the loop and retains its value after loop ends

B The loop variable is global to the entire function

C The loop variable is local to the FOREACH loop block only

D The loop variable is automatically declared and cannot be accessed outside the loop

💡 Hint

Consider variable visibility rules in PL/pgSQL blocks.

In PL/pgSQL, the FOREACH loop variable must be declared outside the loop beforehand. It retains its value from the last iteration after the loop ends.