Today I Learned

hashrocket A Hashrocket project

Fizzbuzz With Common Table Expressions

In learning about CTEs (common table expressions) in postgres, I discovered that you can do some interesting and powerful things using the with recursive construct. The following solves the fizzbuzz problem for integers up to 100

with recursive fizzbuzz (num,val) as (
    select 0, ''
    union
    select (num + 1),
      case
      when (num + 1) % 15 = 0 then 'fizzbuzz'
      when (num + 1) % 5  = 0 then 'buzz'
      when (num + 1) % 3  = 0 then 'fizz'
      else (num + 1)::text
      end
    from fizzbuzz
    where num < 100
)
select val from fizzbuzz where num > 0;

Check out With Queries (Common Table Expressions) for more details on CTEs.

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.