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 SQLite 2, 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 omit the 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 else could 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
