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
pgsqlis as simple as:
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
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;
FizzBuzzmust to be handled as a different conditional from the one that tests for
But PostgreSQL has also recursive CTEs, and things get more interesting.
The idea is pretty simple: the
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;
nrecursive CTE provides a list of one hundred numbers with the strings
Buzzor both as a set of rows. Now, such strings must be concatenated, and here comes
coalescefunction gets the first value that is not
NULL. If both
NULLthey are concatenated into the
FizzBuzzstring. Otherwise, either
NULL(but not both), and therefore one of them passes. If none
Buzzis set, then the regular number is printed as last resort. As you can imagine, the output is similar to:
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.
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