As an exercise, I've written the following query to implement [FizzBuzz][1].
SELECT COALESCE(fizz || buzz, fizz, buzz, '' || n) AS fizzbuzz
FROM (
SELECT n0 + 3 * n3 + 9 * n9 + 27 * n27 + 81 * n81 AS n
FROM
(SELECT 0 AS n0 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n0) AS N0,
(SELECT 0 AS n3 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n3) AS N3,
(SELECT 0 AS n9 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n9) AS N9,
(SELECT 0 AS n27 UNION ALL SELECT 1 UNION ALL SELECT 2 AS n27) AS N27,
(SELECT 0 AS n81 UNION ALL SELECT 1 AS n81) AS N81
) AS N
LEFT OUTER JOIN
(SELECT 3 AS fizzstep, CAST('Fizz' AS CHAR(4)) AS fizz) AS Fizz
ON n % fizzstep = 0
LEFT OUTER JOIN
(SELECT 5 AS buzzstep, CAST('Buzz' AS CHAR(4)) AS buzz) AS Buzz
ON n % buzzstep = 0
WHERE n BETWEEN 1 AND 100
ORDER BY n;
I realize that it could be vastly simplified using GENERATE_SERIES(), but I'm aiming for the solution to be portable to
SQLite2, SQLite 3, and MySQL as well.
I'd like to know, why are the two explicit casts necessary? Casting to VARCHAR or to TEXT also works. However, if I
omitthe casts, I get…
ERROR: failed to find conversion function from unknown to text: …
I would expect that PostgreSQL should be able to infer that the fizz and buzz columns were some kind of text. (What
elsecould they be?) It seems like a design flaw to require a literal string to be cast to text, right?
[1]: http://en.wikipedia.org/wiki/Fizz_buzz