Greg Sabino Mullane: Fun with Postgres Text File Mazes, Charts, and Routes

Disclaimer This article will contain spoilers both on how I solved 2022 Day 22's challenge "Monkey Map" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. AOC Day 22 Tech used: The file_fdw extension to read the input Unlogged tables Sequences Building and modifying arrays via regexp_split_to_array and array_remove More ASCII animation! The first step is to read the text-based input file into a Postgres table: CREATE EXTENSION if not exists file_fdw; CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw; DROP SCHEMA if exists aoc2022_day22_monkeymap CASCADE; CREATE SCHEMA aoc2022_day22_monkeymap; SET search_path = aoc2022_day22_monkeymap; CREATE FOREIGN TABLE aoc_day22 (line text) SERVER aoc2022 options(filename '/tmp/aoc2022.day22.input' -- SERVER aoc2022 options(filename '/tmp/aoc2022.day22.testinput' ); AOC Day 22 - Part One This puzzle asks us to chart a route through a maze, following specific directions about how far to walk and when to turn. The input file looks like this: ...# .#.. #... .... ...#.......# ........#... ..#....#.... ..........#. ...#.... .....#.. .#...... ......#. 10R5L5R10L4R5L5 This is the small test file: the actual one is always much larger and more complex. We can see it is divided into two parts: the maze, and the instructions. Our first step will be to translate that input into SQL tables. For now, we will only focus on the map part, which we will put into a new table: CREATE UNLOGGED TABLE monkeymap ( id INT GENERATED ALWAYS AS IDENTITY, y SMALLINT, x SMALLINT, item CHAR(1), eswn TEXT[] ); We will need some supporting sequences, and then we can read the file line for line and transform it into the columns above: CREATE SEQUENCE aoc; CREATE SEQUENCE aoc2; WITH x AS (SELECT nextval('aoc') AS myrow, setval('aoc2',1,false), line[...]