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:
CREATE OR REPLACE FUNCTION
fizzbuzz( start_number int DEFAULT 1, end_number int DEFAULT 100 )
RETURNS VOID
AS
$CODE$
DECLARE
current_number int;
current_value text;
BEGIN
-- 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;
END IF;
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;
END LOOP;
END
$CODE$
LANGUAGE plpgsql;
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.
WITH RECURSIVE n AS (
SELECT 1 AS current_number, NULL AS mod_3, NULL AS mod_5
UNION
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,
mod_3,
mod_5,
current_number::text )
FROM n;
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