Shall We Play a Game? (with CTEs)

Date: 2016-09-14
Time: 16:30 - 17:20
Room: Ft. Worth 2
Level: Intermediate

Maybe you played the card game War as a child, but you've likely avoided it as an adult. All the skill in the world will never increase your probability of winning beyond that of a coin flip. But even if you have played it recently, I'm almost positive that you've never considered writing a simulation (computer vs. computer) of the least not in SQL.

Common table expressions on their own are an excellent tool for SQL query composition. Luckily for us PostgreSQL takes them one giant step further by providing support for recursion in CTEs. So for this talk I've taken the challenge and written a simulation of War in SQL. Then I had to rewrite it two more times just to find a solution that fit within the limitations PG places on recursive CTEs. We'll begin by considering how to frame the problem space and then work through each of my "solutions". Along the way we'll discover several of the unexpected limitations of recursive CTEs and learn how to use them correctly.


James Coleman