FizzBuzz (in both plpgsql and SQL)

FizzBuzz is something used as straight question during job interviews: the idea is that if you cannot get the alghoritm fine, you are not a programmer at all!
The alghoritm can be described as:

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

Now, how hard could it be? You can find my implementation here. Well, implementing using pgsql is as simple as:
fizzbuzz( start_number int DEFAULT 1, end_number int DEFAULT 100 )
  current_number int;
  current_value  text;
  -- check arguments
  IF start_number >= end_number THEN
     RAISE EXCEPTION 'The start number must be lower then the end one! From % to %', start_number, end_number;

  FOR current_number IN start_number .. end_number LOOP
      current_value := NULL;

      IF current_number % 3 = 0 THEN
         current_value := 'Fizz';
      END IF;
      IF current_number % 5 = 0 THEN
         current_value := coalesce( current_value, '' ) || 'Buzz';
      END IF;

      IF current_value IS NULL THEN
         current_value := current_number::text;
      END IF;

      RAISE INFO '% -> %', current_number, current_value;
LANGUAGE plpgsql;
This is a possible implementation, as you can see there is more code to test input than to effectively do the work. The only trick, in my opinion, in FizzBuzz is that the case that prints FizzBuzz must to be handled as a different conditional from the one that tests for Fizz or `Buzz**.

But PostgreSQL has also recursive CTEs, and things get more interesting.
     SELECT 1 AS current_number, NULL AS mod_3, NULL AS mod_5
     SELECT current_number + 1 as current_number
            , CASE ( current_number + 1 ) % 3 WHEN 0 THEN 'Fizz'
                                              ELSE NULL
                                              END AS mod_3
           , CASE ( current_number + 1 ) % 5 WHEN 0 THEN 'Buzz'
                                              ELSE NULL
                                              END AS mod_5
     FROM n WHERE current_number < 99
SELECT current_number, 
       coalesce( mod_3 || mod_5, 
                 current_number::text )
The idea is pretty simple: the n recursive CTE provides a list of one hundred numbers with the strings Fizz, or Buzz or both as a set of rows. Now, such strings must be concatenated, and here comes coalesce. The coalesce function gets the first value that is not NULL. If both mod_3 and mod_5 are not NULL they are concatenated into the FizzBuzz string. Otherwise, either mod_3 or mod_5 is not NULL (but not both), and therefore one of them passes. If none Fizz or Buzz is set, then the regular number is printed as last resort. As you can imagine, the output is similar to:
 current_number | coalesce 
              1 | 1
              2 | 2
              3 | Fizz
              4 | 4
              5 | Buzz
              6 | Fizz
              7 | 7
              8 | 8
              9 | Fizz
             10 | Buzz
             11 | 11
             12 | Fizz
             13 | 13
             14 | 14
             15 | FizzBuzz
I’m sure there are tons of other implementations, smarter than the above ones. However, what I was interested in demonstrating here was the capability to implement such an alghoritm with PostgreSQL facilities.

The article FizzBuzz (in both plpgsql and SQL) has been posted by Luca Ferrari on June 11, 2019