Last active
September 12, 2015 16:40
-
-
Save bhavinkamani/c98f1ea584d97266a9c6 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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