There have been many FizzBuzz questions lately, and here is one more! Granted, it is pretty straightforward to do FizzBuzz with SQL using calculations.
So instead, I'm going to do a FizzBuzz which will not only be functional, but demonstrate some of the core functions of SQL in general and MySQL PostgreSQL in particular. To make it more fun, we will work with 5 keywords instead of 2: Fizz, Buzz, Woof, Ping, Plop, and they will be user-assigned.
There will be multiple scripts so just bear with me. Please note I'm using Pascal notation throughout.
Create the original schema
DROP SCHEMA IF EXISTS PhrancisFizzBuzz CASCADE;
CREATE SCHEMA PhrancisFizzBuzz;
-- this table will hold the list of numbers to check for FizzBuzz
CREATE TABLE PhrancisFizzBuzz.Number(
n INT NOT NULL
);
-- the following tables will hold multiplier values based on table PhrancisFizzBuzz.Number
CREATE TABLE PhrancisFizzBuzz.Fizz(
Number INT,
Word VARCHAR(4) NOT NULL DEFAULT 'Fizz'
);
CREATE TABLE PhrancisFizzBuzz.Buzz(
Number INT,
Word VARCHAR(4) NOT NULL DEFAULT 'Buzz'
);
CREATE TABLE PhrancisFizzBuzz.Woof(
Number INT,
Word VARCHAR(4) NOT NULL DEFAULT 'Woof'
);
CREATE TABLE PhrancisFizzBuzz.Ping(
Number INT,
Word VARCHAR(4) NOT NULL DEFAULT 'Ping'
);
CREATE TABLE PhrancisFizzBuzz.Plop(
Number INT,
Word VARCHAR(4) NOT NULL DEFAULT 'Plop'
);
Populate FizzBuzz tables
Now I will start creating procedures to do the work.
CREATE OR REPLACE FUNCTION PhrancisFizzBuzz.fncPopulateFizzBuzz(
prmMaxNumber INT, -- for example 100
prmFizz INT,
prmBuzz INT,
prmWoof INT,
prmPing INT,
prmPlop INT
) RETURNS VOID
AS -- begin literal string with escape $$
$$
BEGIN
SET SEARCH_PATH = PhrancisFizzBuzz;
-- initialize and populate list of initial numbers
TRUNCATE TABLE Number;
INSERT INTO Number (n)
SELECT * FROM GENERATE_SERIES(1, prmMaxNumber);
-- populate the FizzBuzz tables with multiples
-- based on user parameter choice
TRUNCATE TABLE Fizz;
INSERT INTO Fizz
SELECT n
FROM Number
WHERE COALESCE(prmFizz,0) <> 0 AND n % prmFizz = 0;
INSERT INTO Buzz
SELECT n
FROM Number
WHERE COALESCE(prmBuzz,0) <> 0 AND n % prmBuzz = 0;
INSERT INTO Woof
SELECT n
FROM Number
WHERE COALESCE(prmWoof,0) <> 0 AND n % prmWoof = 0;
INSERT INTO Ping
SELECT n
FROM Number
WHERE COALESCE(prmPing,0) <> 0 AND n % prmPing = 0;
INSERT INTO Plop
SELECT n
FROM Number
WHERE COALESCE(prmPlop,0) <> 0 AND n % prmPlop = 0;
END;
$$ -- end literal string
LANGUAGE plpgsql;
For testing:
SET SEARCH_PATH = PhrancisFizzBuzz;
SELECT fncPopulateFizzBuzz(100,3,5,0,0,0);
SELECT * FROM Number
LEFT JOIN Fizz ON Number.n = Fizz.Number
LEFT JOIN Buzz ON Number.n = Buzz.Number
ORDER BY Number.n;
Result sample:
Total query runtime: 17 ms.
100 rows retrieved.
FizzBuzz into a table with JOIN
This displays result set into a table with a row for each word:
CREATE OR REPLACE FUNCTION PhrancisFizzBuzz.fcnTableFizzBuzz(
prmMaxNumber INT, -- for example 100
prmFizz INT,
prmBuzz INT,
prmWoof INT,
prmPing INT,
prmPlop INT
) RETURNS VOID
AS
$$
BEGIN
SET SEARCH_PATH = PhrancisFizzBuzz;
-- call populate function with input parameters
PERFORM fncPopulateFizzBuzz(
prmMaxNumber,
prmFizz,
prmBuzz,
prmWoof,
prmPing,
prmPlop
);
DROP TABLE IF EXISTS tmpFizzBuzzResults;
CREATE TEMP TABLE tmpFizzBuzzResults AS
SELECT num.n AS Number,
Fizz.Word AS Fizz,
Buzz.Word AS Buzz,
Woof.Word AS Woof,
Ping.Word AS Ping,
Plop.Word AS Plop
FROM Number AS num
LEFT JOIN Fizz ON Fizz.Number = num.n
LEFT JOIN Buzz ON Buzz.Number = num.n
LEFT JOIN Woof ON Woof.Number = num.n
LEFT JOIN Ping ON Ping.Number = num.n
LEFT JOIN Plop ON Plop.Number = num.n
;
END;
$$
LANGUAGE plpgsql;
For testing:
SET SEARCH_PATH = PhrancisFizzBuzz;
SELECT fcnTableFizzBuzz(100,3,5,7,11,13);
SELECT * FROM tmpFizzBuzzResults ORDER BY Number ASC;
Output sample:
Total query runtime: 78 ms.
100 rows retrieved.
I did try to work in RETURN TABLE with the function but could not get it to work. Any advice on that is welcome.
Last but not least, this generates the classic FizzBuzz:
CREATE OR REPLACE FUNCTION PhrancisFizzBuzz.fcnClassicFizzBuzz(
prmMaxNumber INT,
prmFizz INT,
prmBuzz INT,
prmWoof INT,
prmPing INT,
prmPlop INT
)
RETURNS VOID
AS
$BODY$
BEGIN
-- populate tables based on user input
-- using previously created function
SET SEARCH_PATH = PhrancisFizzBuzz;
PERFORM fncPopulateFizzBuzz(
prmMaxNumber,
prmFizz,
prmBuzz,
prmWoof,
prmPing,
prmPlop
);
DROP TABLE IF EXISTS tmpFizzBuzzResults;
CREATE TEMP TABLE tmpFizzBuzzResults AS
WITH cteFizzBuzzConcat AS(
SELECT Number.n AS Number,
CONCAT(Fizz.Word, Buzz.Word, Woof.Word, Ping.Word, Plop.Word) AS Word
FROM Number
LEFT JOIN Fizz ON Number.n = Fizz.Number
LEFT JOIN Buzz ON Number.n = Buzz.Number
LEFT JOIN Woof ON Number.n = Woof.Number
LEFT JOIN Ping ON Number.n = Ping.Number
LEFT JOIN Plop ON Number.n = Plop.Number
)
SELECT Number
, (CASE
WHEN Word <> '' THEN Word
-- WHEN Word IS NOT NULL THEN Word
ELSE CAST(Number AS VARCHAR)
END) AS Value
FROM cteFizzBuzzConcat;
END;
$BODY$ LANGUAGE plpgsql;
-- For testing:
SET SEARCH_PATH = PhrancisFizzBuzz;
SELECT fcnClassicFizzBuzz(100,3,5,7,11,13);
SELECT Value FROM tmpFizzBuzzResults ORDER BY Number ASC;
Execution:
Total query runtime: 53 ms.
100 rows retrieved.
Output sample:
Update
I ran the last function over 100,000 numbers and it executed in 3.5 seconds.
concat()was introduced in PostgreSQL 9.1. \$\endgroup\$