Tired of FizzBuzz yet?

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
TRUNC

Proliferation of tables

Database schemas should not be designed such that you need to create more tables to accommodate more data. The same principle applies here. Instead of separate tables Fizz, Buzz, Woof, Ping, and Plop, you should have a single NoiseDefs table. A classic unparameterized FizzBuzz should be much simpler…

WITH NoiseDefs(multiple, noise) AS (
    VALUES (3, 'Fizz')
         , (5, 'Buzz')
), CombinedNoises AS (
    SELECT n
         , string_agg(noise, '' ORDER BY multiple) AS noise
        FROM generate_series(1, 100) AS n
            LEFT OUTER JOIN NoiseDefs
                ON n % multiple = 0
        GROUP BY n
)
SELECT n
     , coalesce(noise, CAST(n AS TEXT)) AS noiseOrNum
    FROM CombinedNoises
    ORDER BY n;

Note the use of VALUES and string_agg().

Prefer TEXT over VARCHAR(n) in PostgreSQL

In PostgreSQL, there is not much point to using VARCHAR(n) rather than TEXT — all it does is cause problems if the string length exceeds the limit. You might as well allow noises with more than four characters.

Misuse of temporary tables in functions

The way your functions interact with temporary tables is non-obvious. Each one of them manipulates temporary tables behind the scenes, and the caller needs to have knowledge of that behaviour in order to use the functions. In other words, it's nuts that SELECT fcnClassicFizzBuzz(100, 3, 5, 7, 11, 13); returns nothing, and you have to retrieve the results separately using SELECT Value FROM tmpFizzBuzzResults ORDER BY Number ASC;. (I think you may have been aware of this problem when you said I did try to work in RETURN TABLE with the function but could not get it to work.)

The fcnClassicFizzBuzz() function populates the Fizz, Buzz, Woof, Ping, and Plop tables, but does not clean up after itself. Therefore, it's leaving junk in those tables as a side-effect. Repeated calls to fcnClassicFizzBuzz() will result in increasingly incorrect output. The only recourse for the user is to issue TRUNCATE Fizz; TRUNCATE Buzz; TRUNCATE Woof; TRUNCATE Ping; TRUNCATE Plop; — you didn't even provide a convenience function to undo fncPopulateFizzBuzz().

If you want to let the multiples be parameterized, it seems fair to also let their associated noises be user-specified as well.

CREATE OR REPLACE FUNCTION FlexibleFizzBuzz(
    max INTEGER,
    n1 INTEGER, noise1 TEXT,
    n2 INTEGER, noise2 TEXT,
    n3 INTEGER, noise3 TEXT,
    n4 INTEGER, noise4 TEXT,
    n5 INTEGER, noise5 TEXT
) RETURNS TABLE(n INTEGER, noiseOrNum TEXT) AS $BODY$
    BEGIN
        RETURN QUERY
        WITH NoiseDefs(multiple, noise) AS (
            VALUES (n1, noise1)
                 , (n2, noise2)
                 , (n3, noise3)
                 , (n4, noise4)
                 , (n5, noise5)
        ), CombinedNoises AS (
            SELECT num
                 , string_agg(noise, '' ORDER BY multiple) AS noise
                FROM generate_series(1, max) AS num
                    LEFT OUTER JOIN NoiseDefs
                        ON num % multiple = 0
                GROUP BY num
        )
        SELECT num
             , coalesce(noise, CAST(num AS TEXT))
            FROM CombinedNoises
            ORDER BY num;
    END;
$BODY$ LANGUAGE plpgsql;

Avoid the use of temporary tables altogether if possible. There's less cleanup to worry about, and less wasted I/O. To cache the results, let the caller do it:

CREATE TEMPORARY TABLE FizzBuzzResults10000 AS
    SELECT *
        FROM FlexibleFizzBuzz(10000, 3, 'Fizz', 5, 'Buzz', 7, 'Woof', 11, 'Ping', 13, 'Plop');

This implementation runs in about half the time as your original code.

Function overloading

It would be nice to provide convenience functions, so that some of the parameters can be omitted. For example:

CREATE OR REPLACE FUNCTION FlexibleFizzBuzz(
    max INTEGER,
    n1 INTEGER, noise1 TEXT,
    n2 INTEGER, noise2 TEXT
) RETURNS TABLE(n INTEGER, noiseOrNum TEXT) AS $BODY$
    BEGIN
    RETURN QUERY SELECT * FROM FlexibleFizzBuzz(
        max,
        n1, noise1,
        n2, noise2,
        NULL, NULL,
        NULL, NULL,
        NULL, NULL);
    END;
$BODY$ LANGUAGE plpgsql;

I don't believe that PL/pgSQL functions can take a variable number of arguments, though.

Hungarian notation

As you can infer from my implementation above, I'm not a fan of the prefixes that you used, such as fnc…, prm…, tmp…, and cte…. Rather, I would use CapitalizedNames for table-like objects, such as tables, views, and functions that return rows. For scalars, use lowercaseNames. I see it as an advantage that you can replace a temporary table with a view, for example. (PostgreSQL identifiers are actually case-insensitive unless you "quote" them, but having that convention in the source code should be sufficient.)

StackExchange Code Review Q#57737, answer score: 30