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.

Practice

1. What is the main purpose of the FOREACH statement in PostgreSQL when working with arrays?

easy

A. To convert an array into a string.

B. To loop through each element of an array easily inside PL/pgSQL.

C. To sort the elements of an array in ascending order.

D. To create a new array from existing tables.

Solution

1. Step 1: Understand FOREACH usage context

   FOREACH is used inside PL/pgSQL to iterate over array elements one by one.

2. Step 2: Identify the main purpose

   It simplifies looping through arrays without manual index handling.

3. Final Answer:

   To loop through each element of an array easily inside PL/pgSQL. -> Option B

4. Quick Check:

   FOREACH loops over array elements [OK]

Hint: FOREACH loops over array elements inside PL/pgSQL [OK]

Common Mistakes:

• Confusing FOREACH with array creation or sorting
• Thinking FOREACH works outside PL/pgSQL
• Assuming FOREACH converts arrays to strings

2. Which of the following is the correct syntax to iterate over an integer array arr using FOREACH in PL/pgSQL?

easy

A. FOREACH element IN ARRAY arr LOOP ... END LOOP;

B. FOREACH element FROM arr LOOP ... END LOOP;

C. FOREACH element ON arr LOOP ... END LOOP;

D. FOREACH element OVER arr LOOP ... END LOOP;

Solution

1. Step 1: Recall FOREACH syntax

   The correct syntax uses 'IN ARRAY' to specify the array to loop over.

2. Step 2: Match syntax options

   Only FOREACH element IN ARRAY arr LOOP ... END LOOP; uses 'IN ARRAY' correctly; others use invalid keywords.

3. Final Answer:

   FOREACH element IN ARRAY arr LOOP ... END LOOP; -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Use 'IN ARRAY' to loop over arrays with FOREACH [OK]

Common Mistakes:

• Using FROM, ON, or OVER instead of IN ARRAY
• Omitting LOOP or END LOOP keywords
• Trying to use FOREACH outside PL/pgSQL

3. Consider the following PL/pgSQL block:

DECLARE
  arr integer[] := ARRAY[2, 4, 6];
  sum integer := 0;
BEGIN
  FOREACH val IN ARRAY arr LOOP
    sum := sum + val;
  END LOOP;
  RAISE NOTICE '%', sum;
END;

What will be the output when this block runs?

medium

A. Syntax error

B. 24

C. 12

D. 0

Solution

1. Step 1: Understand the loop iteration

   The FOREACH loops over arr elements: 2, 4, and 6.

2. Step 2: Calculate the sum

   sum starts at 0, then adds 2 + 4 + 6 = 12.

3. Final Answer:

   12 -> Option C

4. Quick Check:

   2 + 4 + 6 = 12 [OK]

Hint: Sum array elements by adding each in FOREACH loop [OK]

Common Mistakes:

• Assuming FOREACH does not add elements
• Confusing sum initialization
• Expecting syntax error due to RAISE NOTICE

4. Identify the error in this PL/pgSQL snippet:

DECLARE
  arr text[] := ARRAY['a', 'b', 'c'];
  ch text;
BEGIN
  FOREACH ch IN arr LOOP
    RAISE NOTICE '%', ch;
  END LOOP;
END;

medium

A. Variable 'ch' must be declared as integer, not text.

B. Array declaration syntax is incorrect.

C. RAISE NOTICE cannot print variables inside loops.

D. FOREACH must use 'IN ARRAY' before the array variable.

Solution

1. Step 1: Check FOREACH syntax

   FOREACH requires 'IN ARRAY' before the array variable to iterate properly.

2. Step 2: Identify the error

   The code uses 'IN arr' instead of 'IN ARRAY arr', causing syntax error.

3. Final Answer:

   FOREACH must use 'IN ARRAY' before the array variable. -> Option D

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Always write 'FOREACH var IN ARRAY arr' [OK]

Common Mistakes:

• Omitting 'ARRAY' keyword in FOREACH
• Wrong variable type for array elements
• Misunderstanding RAISE NOTICE usage

5. You want to write a PL/pgSQL function that takes an integer array and returns the count of even numbers using FOREACH. Which of the following code snippets correctly implements this?

hard

A. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

B. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val FROM arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

C. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOR val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

D. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val ON ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

Solution

1. Step 1: Check FOREACH syntax correctness

   CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$ DECLARE val integer; count integer := 0; BEGIN FOREACH val IN ARRAY arr LOOP IF val % 2 = 0 THEN count := count + 1; END IF; END LOOP; RETURN count; END; $$ LANGUAGE plpgsql; uses 'FOREACH val IN ARRAY arr LOOP', which is the correct syntax.

2. Step 2: Verify logic for counting even numbers

   Inside the loop, it checks if val is even and increments count correctly.

3. Final Answer:

   FOREACH val IN ARRAY arr LOOP with IF val % 2 = 0 logic -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... and correct IF condition [OK]

Hint: Use 'FOREACH val IN ARRAY arr' and check val % 2 = 0 [OK]

Common Mistakes:

• Using 'FROM' or 'ON ARRAY' instead of 'IN ARRAY'
• Confusing FOREACH with FOR loops
• Missing RETURN statement or wrong logic