PL/pgSQL Conway's game of life


I recently started learning the details of PL/pgSQL. It actually started with me wanting to learn Oracle PL/SQL but giving up after looking at licensing costs and having a stellar time not getting Oracle XE running on a debian box.

The Oracle fetish makes no sense because I use PostgreSQL every day at work, interest in Ada for defense contracting work, and its got similarities to PL/SQL for free anyway. And you can run it on a free amazon RDS if you're lazy.

Learning things like PL/pgSQL, T-SQL etc is really useful even if you only have a laymans handling of the database side of your tech stack because you can greatly reduce the amount of round trips in your data access objects/repos/entities and implement 'data logic' with no client/server roundtrips. Being able to efficiently create a table that matches your DTO will make the poor soul who is cranking out spring code really like you, too.

It's well known on the internet if you want to solicit advice from experts for free you can do a few things:

  1. claim another technology is superior due to issues you're running into
  2. find the most egregious misuse of the thing you can possibly think of

This post is mostly about the latter - implementing Conway's game of life in naive PL/pgSQL functions. It's a good candidate (of misuse) because the game rules are simple and can be represented easily by an array. Processing large arrays in PL/pgSQL is not a good idea. Perfect!


The idea is simple, the game state is represented by a bool[] type which is actually multi-dimensioned to the universe size. eg, a 3x3 universe would look like this (in postgres array literal format):

The majority of the code is size x size loops filling out/reading the array. There is nothing too special about an of the code. The neighbor counting and rules logic are probably the only interesting pieces.

After running the full script, to generate a game run the following query:

SELECT life.game_of_life(12);

where 12 is the board size you would like. note the game output will be in your SQL log, not the query result. It's currently hard coded to be uniform size but could easily be changed. One limitation is changing size requires dropping the current state table to really work right.

In usage it should look something like this (pictured is datagrip). life queries

The print_state function uses 'X' for live cells and ' ' for dead, this should probably be something better!

Below is the full code, or the gist.

October 30, 2016 · Database · PostgreSQL

Previous:TravisCI PostgreSQL flyway migrations
Next:Rooting steamlink pt1