FOREACH for array iteration in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the FOREACH statement do in PostgreSQL?

FOREACH loops through each element in an array, allowing you to perform actions on each item one by one.

beginner

How do you declare a FOREACH loop to iterate over an integer array in PostgreSQL?

Use: FOREACH element_variable IN ARRAY array_variable LOOP ... END LOOP;

intermediate

Can FOREACH be used to iterate over arrays of types other than integers?

Yes, FOREACH works with arrays of any data type, like text[], integer[], or custom types.

beginner

What happens if the array used in FOREACH is empty?

The loop body does not execute at all because there are no elements to iterate over.

intermediate

Why is FOREACH useful compared to a traditional FOR loop in PostgreSQL?

FOREACH simplifies looping over arrays directly without needing to manage index counters manually.

What keyword starts the loop to iterate over an array in PostgreSQL?

A WHILE

B FOR

C FOREACH

D LOOP

FOREACH is the specific keyword used to loop over each element in an array.

Which of these is the correct syntax to iterate over an array named my_array?

A FOREACH item IN ARRAY my_array LOOP ... END LOOP;

B FOREACH item IN my_array LOOP ... END LOOP;

C FOR item IN ARRAY my_array LOOP ... END LOOP;

D FOR item IN my_array LOOP ... END LOOP;

FOREACH requires the IN ARRAY clause to specify the array to iterate over.

What type of variable should 'item' be in FOREACH item IN ARRAY my_array?

A Same type as elements in my_array

B Any type

C Always integer

D Always text

The loop variable must match the data type of the array elements.

If my_array is empty, what happens inside the FOREACH loop?

A Loop runs infinitely

B Loop runs once with NULL

C Error occurs

D Loop runs zero times

No elements means the loop body does not execute.

FOREACH is best used when you want to:

A Loop a fixed number of times

B Process each element of an array easily

C Loop through table rows

D Create arrays

FOREACH is designed to iterate over array elements simply.

Explain how to use FOREACH to loop through an array in PostgreSQL.

Think about the syntax and what each part means.

Describe a real-life scenario where FOREACH for array iteration would be helpful in a database function.

Imagine you have a list of things to handle one by one.