Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
CREATE TABLE board(id serial primary key, moves int[]);
INSERT INTO board(moves) values('{0,0,0}');
WITH current AS (SELECT generate_series(1,3) p_no, unnest(moves) "pos" FROM board order by id desc LIMIT 3),
roll AS (SELECT generate_series(1,3) p_no, round(random()*2)+1 result),
move AS (SELECT coalesce('{"9":"2","3":"6"}'::json->>(c.pos+r.result)::text, (c.pos+r.result)::text)::int "new_pos", c.pos "old_pos"
FROM current c JOIN roll r ON c.p_no = r.p_no),
constrained_move AS (SELECT (CASE WHEN m.new_pos > 10 THEN m.old_pos
WHEN m.new_pos < 10 THEN m.new_pos
ELSE 10 END) "final_pos" FROM move m),
rec AS (INSERT INTO board(moves) (SELECT array_agg(cm.final_pos) FROM constrained_move cm))
SELECT * FROM board;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment