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:
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:
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);
..Back to Dexter Haslem home