game of life in PL/pgSQL

Background

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 effenciently 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!

Implementation

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):

{{false,false,false},{false,false,false},{false,false,false}}

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).

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.

CREATE SCHEMA IF NOT EXISTS life;
SET search_path = life;

CREATE TABLE IF NOT EXISTS life.state (
generation SERIAL PRIMARY KEY NOT NULL,
--size       INT             NOT NULL CHECK (size >= 3),
state      BOOL []
);

CREATE OR REPLACE FUNCTION life.neighbor_alive_count(u BOOL [], x INT, y INT)
RETURNS INT AS $$
DECLARE
ac INT := 0;
BEGIN
FOR xo IN -1..1 LOOP
    FOR yo IN -1..1 LOOP
    IF xo = 0 AND yo = 0
    THEN
        -- dont count ourselves as a neighbor
        CONTINUE;
    END IF;
    -- we get off easy here, going out of bounds returns <NULL>
    IF u [x + xo] [y + yo]
    THEN
        ac := ac + 1;
    END IF;
    END LOOP;
END LOOP;

RETURN ac;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION life.next_cell_state(cur BOOL, alive_nb INT)
RETURNS BOOL AS $$
BEGIN
RETURN (NOT cur AND alive_nb = 3) OR alive_nb = 2 OR alive_nb = 3;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION life.next_universe(cur_uv BOOL [])
RETURNS BOOL [] AS $$
DECLARE
xl     INT := array_length(cur_uv, 1);
yl     INT := array_length(cur_uv, 2);
new_uv BOOL [] := array_fill(FALSE, ARRAY [xl, yl]);
BEGIN
FOR x IN 1..xl LOOP
    FOR y IN 1..yl LOOP
    new_uv [x] [y] = life.next_cell_state(cur_uv [x] [y], life.neighbor_alive_count(cur_uv, x, y));
    END LOOP;
END LOOP;
RETURN new_uv;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION life.create_universe(size INT, fillrandom BOOL)
RETURNS BOOL [] AS $$
DECLARE
ret BOOL [] := NULL;
BEGIN
ret = array_fill(FALSE, ARRAY [size, size]);
IF fillrandom
THEN
    FOR x IN 1..size LOOP
    FOR y IN 1..size LOOP
        ret [x] [y] = (random() * 100) :: INT % 2 = 0;
    END LOOP;
    END LOOP;
END IF;
RETURN ret;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION life.print_state(state BOOL [])
RETURNS VOID AS $$
DECLARE
line TEXT := '';
BEGIN
FOR x IN 1..array_length(state, 1) LOOP
    FOR y IN 1..array_length(state, 2) LOOP
    IF state [x] [y]
    THEN
        line := concat(line, 'X ');
    ELSE
        line := concat(line, '  ');
    END IF;
    END LOOP;
    RAISE NOTICE '%', line;
    line := '';
END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW life.most_recent_state AS
SELECT s.state
FROM life.state s
ORDER BY s.generation DESC
LIMIT 1;

CREATE OR REPLACE FUNCTION life.game_of_life(boardsize INT)
RETURNS VOID AS $$
BEGIN

IF NOT exists(SELECT 1
                FROM life.most_recent_state)
THEN
    INSERT INTO life.state (state) SELECT life.create_universe(boardsize, TRUE);
ELSE
    INSERT INTO life.state (state) SELECT life.next_universe((SELECT *
                                                            FROM life.most_recent_state));
END IF;

PERFORM life.print_state(boardsize, (SELECT *
                                    FROM life.most_recent_state));
END;
$$ LANGUAGE plpgsql;


--SELECT life.game_of_life(6);