Nested Loops and Recursion

Nested loops are loops that run inside other loops. Imagine an interview Q&A session, where one question leads to more follow-up questions. The logic of nested loops is like this: the outer loop does its iteration, and for each iteration, the inner loop runs through its own set of passes.

Example: Multiplication Table

Let's write a function that creates a multiplication table for numbers from 1 to 5. This is a classic example where nested loops come in handy:

CREATE OR REPLACE FUNCTION generate_multiplication_table()
RETURNS VOID AS $$
BEGIN
  FOR i IN 1..5 LOOP -- Outer loop
    FOR j IN 1..5 LOOP -- Inner loop
      RAISE NOTICE '% x % = %', i, j, i * j; -- Logging the result
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Calling the function:
SELECT generate_multiplication_table();

How it works:

1. The outer loop grabs values of i from 1 to 5.
2. For each value of i, the inner loop takes values of j from 1 to 5.
3. On every step, i and j are combined to calculate the multiplication result.
4. The output looks like a mini-table:

   1 x 1 = 1
   1 x 2 = 2
   ...
   5 x 5 = 25

Practice: Finding Intersections in Two Tables

Now let's make a more "real-life" example. Imagine we have two tables:

• students (students, their names),
• courses (courses they're enrolled in).

We want to find students who are enrolled in more than one course. Let's use nested loops:

CREATE OR REPLACE FUNCTION find_students_with_multiple_courses()
RETURNS TABLE(student_name TEXT, course_name TEXT) AS $$
DECLARE
    stud RECORD;
    crs  RECORD;
BEGIN
    FOR stud IN
        SELECT s.id, s.name
        FROM students s
        WHERE (SELECT COUNT(*) FROM courses c WHERE c.student_id = s.id) > 1
    LOOP
        FOR crs IN
            SELECT c.name FROM courses c WHERE c.student_id = stud.id
        LOOP
            student_name := stud.name;
            course_name  := crs.name;
            RETURN NEXT;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Calling the function:
SELECT * FROM find_students_with_multiple_courses();

Recursion

Recursion is when a function calls itself. It's like if you asked your friend to explain SQL, and they told you to read the docs, which point you right back to this lesson... Don't mix up recursion with an infinite loop. Recursion always has a "stop condition" (the point where the function stops calling itself).

Example: Calculating the Factorial of a Number

The factorial of a number n is the product of all numbers from 1 to n. For example, the factorial of 5 (written as 5!) is 5 * 4 * 3 * 2 * 1 = 120. Here's how you can do it with recursion:

CREATE OR REPLACE FUNCTION calculate_factorial(n INTEGER)
RETURNS INTEGER AS $$
BEGIN
  -- Stop condition: factorial of 0 or 1 is 1
  IF n = 0 OR n = 1 THEN
    RETURN 1;
  END IF;

  -- Recursive call
  RETURN n * calculate_factorial(n - 1);
END;
$$ LANGUAGE plpgsql;

-- Calling the function:
SELECT calculate_factorial(5); -- Result: 120

How it works:

1. If n is 0 or 1, return 1.
2. If n > 1, the function calls itself with n - 1 and multiplies that value by n.
3. So, the calls "stack up" and then collapse back down in one direction.

Practical Example: Fibonacci Numbers

Fibonacci numbers are a sequence where each number is the sum of the two previous ones. The sequence starts like this: 0, 1, 1, 2, 3, 5, 8....

Let's write a function to calculate the n-th number in the sequence:

CREATE OR REPLACE FUNCTION fibonacci(n INTEGER)
RETURNS INTEGER AS $$
BEGIN
  -- Stop condition: the first two numbers are known
  IF n = 0 THEN
    RETURN 0;
  ELSIF n = 1 THEN
    RETURN 1;
  END IF;

  -- Recursive call
  RETURN fibonacci(n - 1) + fibonacci(n - 2);
END;
$$ LANGUAGE plpgsql;

-- Calling the function:
SELECT fibonacci(6); -- Result: 8

When Should You Use Nested Loops and Recursion?

1. Nested loops are great for working with tables:

   • Comparing values between two tables.
   • Building complex data combinations.

2. Recursion is better for:

   • Calculating sequences (like factorials, Fibonacci numbers).
   • Working with hierarchical structures (like a product category tree).

Common Mistakes

Nested loops can sometimes be "expensive" in terms of performance, especially when working with big tables. Only use them when you can't get the result with regular SQL tools.

When using recursion, make sure you have a clear "stop condition." Without it, you'll get an infinite function call and probably a stack overflow error.

Complex nested structures can make debugging a pain. Use RAISE NOTICE to print out intermediate results and help yourself out.

2

Task

SQL SELF, level 51, lesson 3

Locked

Simple multiplication table using nested loops

Simple multiplication table using nested loops

Open