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
FROM aoc_day22 WHERE line !~ '\d')
,y AS materialized (SELECT *, string_to_table(line, null) AS a FROM x)
,z AS (SELECT *, nextval('aoc2') AS mycol FROM y)
INSERT INTO monkeymap (y,x,item)
SELECT myrow, mycol, a FROM z WHERE a <> ' ';
In the CTE above, we first use "x" to read one line at a time from our text file, using the sequence "aoc" to represent the row number, and resetting our column number "aoc2" to 1. Next we use "y" to break that line apart character by character. Then with "z" we gather all the items from y, along with incrementing the column number "aoc2" for each item. Finally, we insert all non-empty spots on the maze into our x,y grid. The final table looks like this for the first two rows:
SELECT * FROM monkeymap where y <= 2 ORDER BY y,x;
id | y | x | item | eswn
----+---+----+------+------
1 | 1 | 9 | . | ☃
2 | 1 | 10 | . | ☃
3 | 1 | 11 | . | ☃
4 | 1 | 12 | # | ☃
5 | 2 | 9 | . | ☃
6 | 2 | 10 | # | ☃
7 | 2 | 11 | . | ☃
8 | 2 | 12 | . | ☃
Because we are going to be consulting this table a lot, we are going to precompute all the possible moves from one location to another, taking into account the special rules about "wrapping" from one end to the other. So each cell (i.e. unique x/y location) will get assigned an array indicating what happens when you move east, south, west, or north from the current cell. Here is the function to do that:
CREATE or replace FUNCTION monkey_premap()
returns INTEGER language plpgsql AS $$
DECLARE
myrec RECORD;
north INT; south SMALLINT; east SMALLINT; west INT;
BEGIN
FOR myrec IN SELECT * FROM monkeymap WHERE item = '.' LOOP
-- north: x the same, y decreases
SELECT INTO north CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x AND y=myrec.y-1;
IF north IS NULL THEN
SELECT INTO north CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x ORDER BY y DESC LIMIT 1;
END IF;
-- south: x the same, y increases
SELECT INTO south CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x AND y=myrec.y+1;
IF south IS NULL THEN
SELECT INTO south CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x ORDER BY y ASC LIMIT 1;
END IF;
-- east: y the same, x increases
SELECT INTO east CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE y=myrec.y AND x=myrec.x+1;
IF east IS NULL THEN
SELECT INTO east CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE y=myrec.y ORDER BY x ASC LIMIT 1;
END IF;
-- west: y the same, x decreases
SELECT INTO west CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE y=myrec.y AND x=myrec.x-1;
IF west IS NULL THEN
SELECT INTO west CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE y=myrec.y ORDER BY x DESC LIMIT 1;
END IF;
UPDATE monkeymap SET eswn = ARRAY[east,south,west,north]
WHERE ctid = myrec.ctid;
END LOOP;
return 1;
END
$$;
Before we run the function, we should create some indexes that the queries in it will benefit from, then analyze the table to generate fresh statistics:
CREATE INDEX monkeyindex ON monkeymap(x,y);
CREATE INDEX monkeyids ON monkeymap(id);
ANALYZE monkeymap;
SELECT monkey_premap();
Our table now looks like this, for the first two rows of "y":
id | y | x | item | eswn
----+---+----+------+------------
1 | 1 | 9 | . | {2,5,0,89}
2 | 1 | 10 | . | {3,0,1,90}
3 | 1 | 11 | . | {0,7,2,91}
4 | 1 | 12 | # | ☃
5 | 2 | 9 | . | {0,0,8,1}
6 | 2 | 10 | # | ☃
7 | 2 | 11 | . | {8,11,0,3}
8 | 2 | 12 | . | {5,12,7,0}
Just how big is the real data set? Even with our indexes, it took around 10 seconds to run that function. Here's what the first few table rows look like:
id | y | x | item | eswn
----+---+----+------+-------------------
1 | 1 | 51 | . | {2,101,100,12451}
2 | 1 | 52 | . | {3,102,1,12452}
3 | 1 | 53 | . | {0,103,2,0}
Finally, we need a function to do the actual walking of the maze, based on the instructions given in the last line of the input file.
CREATE or replace FUNCTION monkeywalk()
RETURNS int language plpgsql AS $$
DECLARE
walk TEXT[]; spin TEXT[];
myid INT;
mydir INT;
myrec RECORD;
j INT = 0;
newdir INT;
BEGIN
/* Stick all of our distance commands into an array */
SELECT INTO walk regexp_split_to_array(line, '\D+')
FROM aoc_day22 WHERE line ~ '\d';
/* Stick all of our direction commands into an array, and trim empty items */
SELECT INTO spin array_remove(regexp_split_to_array(line, '\d+'),'')
FROM aoc_day22 WHERE line ~ '\d';
/* We always start in the top row, on the far left, facing east */
SELECT INTO myid, mydir id,1 FROM monkeymap
WHERE y=1 AND item='.' ORDER BY x ASC LIMIT 1;
UPDATE monkeymap SET item = '>' WHERE id = myid;
WHILE walk[j+1] IS NOT NULL LOOP
j = j + 1;
/* First, we walk as far as we can */
FOR m IN 1 .. walk[j] LOOP
/* What is in this direction? */
SELECT eswn[mydir] INTO newdir FROM monkeymap WHERE id = myid;
/* If we hit a wall, stop walking and go to the rotation */
IF newdir = 0 THEN EXIT; END IF;
/* Move to the new location */
myid = newdir;
END LOOP;
/* Done walking, so time to rotate left or right */
IF spin[j] IS NULL THEN EXIT; END IF;
IF spin[j] = 'L' THEN
mydir = CASE WHEN mydir = 1 THEN 4 ELSE mydir-1 END;
ELSE
mydir = CASE WHEN mydir = 4 THEN 1 ELSE mydir+1 END;
END IF;
END LOOP;
/* Finished - display the final score */
RETURN (y * 1000) + (x * 4) + (mydir-1) FROM monkeymap WHERE id = myid;
END
$$;
When we run it, we get the correct answer in about 1.7 seconds:
SELECT monkeywalk();
monkeywalk
------------
186128
AOC Day 22 - Part Two
Part two gets...tricky. Rather than a simple two-dimensional map, we find ourselves holding a three dimensional cube which has been flattened out. So our map actually works like this:
1111
1111
1111
1111
222233334444
222233334444
222233334444
222233334444
55556666
55556666
55556666
55556666
Each of the numbers represents a different face of the cube. Of course, all of the movement rules are different now too, as walking off the "edge" of one face of the cube makes you appear on another face, with a new orientation! I tried really hard to solve this mentally by just looking at the map, but eventually had to create a small paper cube to keep everything straight and derive the correct rules as we moved from face to face.
Our first step will be to reset our initial table, as we need things to not be affected by any updates we did in part one:
TRUNCATE TABLE monkeymap;
SELECT setval('aoc',1,false);
WITH x AS (SELECT nextval('aoc') AS myrow, setval('aoc2',1,false), line
FROM aoc_day22 WHERE line !~ '\d')
,y AS materialized (SELECT *, string_to_table(line, null) AS a FROM x)
,z AS (SELECT *, nextval('aoc2') AS mycol FROM y)
INSERT INTO monkeymap (y,x,item)
SELECT myrow, mycol, a FROM z WHERE a <> ' ';
We need to add some more columns to track new information. Each side of the cube will be represented by a letter from A to F. Everytime we go over the edge from one face to another, our orientation on the 2-D map may change, so we also need to record what sort of "twist" things take when we do so. Finally, we make a "xy" column as a shorthand array of our x and y coordinates.
ALTER TABLE monkeymap ADD COLUMN z CHAR, ADD COLUMN twist TEXT[];
ALTER TABLE monkeymap ADD COLUMN xy INT[];
UPDATE monkeymap SET xy= ARRAY[x,y];
Next, we need to map each cell, or original x/y coordinate, to one of the faces. This depends heavily on how the cube is folded. The solution below is optimized for my real data, not the test data. That's why each cube face is 50x50 characters wide.
\set Q 50
UPDATE monkeymap SET z =
CASE WHEN y <= :Q AND x <= (:Q*2) THEN 'A'
WHEN y <= :Q AND x > (:Q*2) THEN 'B'
WHEN y BETWEEN :Q+1 AND :Q*2 THEN 'C'
WHEN y BETWEEN 1+(:Q*2) AND :Q*3 AND x <= :Q THEN 'D'
WHEN y BETWEEN 1+(:Q*2) AND :Q*3 AND x > :Q THEN 'E'
WHEN y >= 1+(:Q*3) THEN 'F' END;
As a sanity check, let's run a GROUP BY and confirm that each face has the same number of cells:
SELECT z, count(*) FROM monkeymap GROUP BY 1 ORDER BY 1;
z | count
---+-------
A | 2500
B | 2500
C | 2500
D | 2500
E | 2500
F | 2500
(6 rows)
It kind of looks like this:
AABB
AABB
CC
CC
DDEE
DDEE
FF
FF
Our table is still a 2-D map which has "holes" that represent places where the cube faces are not. In other words, we now need to fold our table into a 3-D space, by very carefully shifting things around. For example, we need to shift the "A" values left by 50. Getting this part just right is where most of the puzzle's time was actually spent!
/* A,C,E gets x-shifted over by Q */
UPDATE monkeymap SET x = x-:Q WHERE z IN ('A','C','E');
/* B gets x-shifted over by 2xQ */
UPDATE monkeymap SET x = x - (:Q*2) WHERE z = 'B';
/* C get y-shifted by Q */
UPDATE monkeymap SET y = y - :Q WHERE z = 'C';
/* D,E get y-shifted by Q*2 */
UPDATE monkeymap SET y = y - (:Q*2) WHERE z IN ('D','E');
/* F gets y-shifted by Q*3 */
UPDATE monkeymap SET y = y - (:Q*3) WHERE z = 'F';
This part was so tricky I wrote a quick custom assertion to sanity check the results. We basically want to ensure that all cells live somewhere between 1 and 50 on both the x and y axis:
CREATE OR REPLACE FUNCTION monkey_assert(INT) RETURNS void
language plpgsql as $$
BEGIN
PERFORM 1 FROM monkeymap WHERE x > $1;
IF FOUND THEN RAISE 'Invalid monkeymap x> %!', $1; END IF;
PERFORM 1 FROM monkeymap WHERE x > $1 OR y > $1 OR x < 1 OR y < 1;
IF FOUND THEN RAISE 'Invalid monkeymap!'; END IF;
END $$;
SELECT monkey_assert(:Q);
At this point, we are ready to write and run a function to walk the cube and generate all the solutions to where we appear for each direction we head from any point, by populating the eswn array. However, unlike the previous time we did this, we also need to account for the fact that we may also change our direction because we walked over the edge from one face to another! So we store that information in a second array called twist. Here's our newarray population function:
CREATE or replace FUNCTION monkeycube(int)
RETURNS int language plpgsql AS $$
DECLARE
myrec RECORD; east SMALLINT; south SMALLINT; west INT; north INT;
teast CHAR; tsouth CHAR; twest CHAR; tnorth CHAR;
maxx SMALLINT = $1;
BEGIN
/* For every spot on the map we could possibly walk to,
figure out what is in each direction, and if we hit an edge */
FOR myrec IN SELECT * FROM monkeymap WHERE item = '.' LOOP
/*
A: e=BE s=CS w=DE n=FE B: e=EW s=CW w=AW n=FN
C: e=BN s=ES w=DS n=AN D: e=EE s=FS w=AE n=CE
E: e=BW s=FW w=DW n=CN F: e=EN s=BS w=AS n=DN
*/
/* Heading east */
teast = '>';
/* Is there a valid space to the east on this cube face? */
SELECT INTO east CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x+1 AND y=myrec.y AND z=myrec.z;
/* If not found, we mus have walked over the edge to a new side of the cube */
IF east IS NULL THEN
SELECT INTO east CASE WHEN item='.' THEN id ELSE 0 END FROM monkeymap WHERE
(myrec.z='A' AND z='B' AND y=myrec.y AND x=1) /* East */
OR (myrec.z='B' AND z='E' AND y=maxx-myrec.y+1 AND x=maxx) /* West USD */
OR (myrec.z='C' AND z='B' AND x= myrec.y AND y=maxx) /* North */
OR (myrec.z='D' AND z='E' AND y=myrec.y AND x=1) /* East */
OR (myrec.z='E' AND z='B' AND y=maxx-myrec.y+1 AND x=maxx) /* West USD */
OR (myrec.z='F' AND z='E' AND x=myrec.y AND y=maxx); /* North */
teast = CASE WHEN myrec.z IN ('C','F') THEN '^'
WHEN myrec.z IN ('B','E') THEN '<' ELSE '>' END;
END IF;
/* Heading south */
tsouth = 'v';
SELECT INTO south CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x AND y=myrec.y+1 AND z=myrec.z;
IF south IS NULL THEN
SELECT INTO south CASE WHEN item='.' THEN id ELSE 0 END FROM monkeymap WHERE
(myrec.z='A' AND z='C' AND x=myrec.x AND y=1) /* South */
OR (myrec.z='B' AND z='C' AND y=myrec.x AND x=maxx) /* West */
OR (myrec.z='C' AND z='E' AND x=myrec.x AND y=1) /* South */
OR (myrec.z='D' AND z='F' AND x=myrec.x AND y=1) /* South */
OR (myrec.z='E' AND z='F' AND y=myrec.x AND x=maxx) /* West */
OR (myrec.z='F' AND z='B' AND x=myrec.x AND y=1); /* South */
tsouth = CASE WHEN myrec.z IN ('B','E') THEN '<' ELSE 'v' END;
END IF;
/* Heading west */
twest = '<';
SELECT INTO west CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE y=myrec.y AND x=myrec.x-1 AND z=myrec.z;
IF west IS NULL THEN
SELECT INTO west CASE WHEN item='.' THEN id ELSE 0 END FROM monkeymap WHERE
(myrec.z='A' AND z='D' AND y=maxx-myrec.y+1 AND x=1) /* East USD */
OR (myrec.z='B' AND z='A' AND y=myrec.y AND x=maxx) /* West */
OR (myrec.z='C' AND z='D' AND x=myrec.y AND y=1) /* South */
OR (myrec.z='D' AND z='A' AND y=maxx-myrec.y+1 AND x=1) /* East USD? */
OR (myrec.z='E' AND z='D' AND y=myrec.y AND x=maxx) /* West */
OR (myrec.z='F' AND z='A' AND x=myrec.y AND y=1); /* South */
twest = CASE WHEN myrec.z IN ('A','D') THEN '>'
WHEN myrec.z IN ('C','F') THEN 'v' ELSE '<' END;
END IF;
/* Heading north */
tnorth = '^';
SELECT INTO north CASE WHEN item = '.' THEN id ELSE 0 END
FROM monkeymap WHERE x=myrec.x AND y=myrec.y-1 AND z=myrec.z;
IF north IS NULL THEN
SELECT INTO north CASE WHEN item='.' THEN id ELSE 0 END FROM monkeymap WHERE
(myrec.z='A' AND z='F' AND y=myrec.x AND x=1) /* East */
OR (myrec.z='B' AND z='F' AND x=myrec.x AND y=maxx) /* North */
OR (myrec.z='C' AND z='A' AND x=myrec.x AND y=maxx) /* North */
OR (myrec.z='D' AND z='C' AND y=myrec.x AND x=1) /* East */
OR (myrec.z='E' AND z='C' AND x=myrec.x AND y=maxx) /* North */
OR (myrec.z='F' AND z='D' AND x=myrec.x AND y=maxx); /* North */
tnorth = CASE WHEN myrec.z IN ('A','D') THEN '>' ELSE '^' END;
END IF;
UPDATE monkeymap SET eswn = ARRAY[east,south,west,north],
twist = ARRAY[teast, tsouth, twest, tnorth]
WHERE ctid = myrec.ctid;
END LOOP;
RETURN 1;
END
$$;
Running this takes about 2 seconds
SELECT monkeycube(:Q);
Finally we can write a function to walk around the outside of the cube:
CREATE or replace FUNCTION monkey_inception()
RETURNS int language plpgsql AS $$
DECLARE
walk TEXT[]; spin TEXT[]; j INT = 0;
myid INT; mydir SMALLINT; newdir INT; newflip CHAR;
myrec RECORD; oldid INT=0; veryoldid INT=0;
BEGIN
/* Stick all of our distance commands into an array */
SELECT INTO walk regexp_split_to_array(line, '\D+')
FROM aoc_day22 WHERE line ~ '\d';
/* Stick all of our direction commands into an array, and trim empty items */
SELECT INTO spin array_remove(regexp_split_to_array(line, '\d+'),'')
FROM aoc_day22 WHERE line ~ '\d';
/* We always start in the top row, on the far left, facing east */
SELECT INTO myid, mydir id,1 FROM monkeymap
WHERE y=1 AND item='.' AND z='A' ORDER BY x ASC LIMIT 1;
WHILE walk[j+1] IS NOT NULL LOOP
j = j + 1;
/* First, we walk as far as we can */
FOR m IN 1 .. walk[j] LOOP
/* What is in this direction? */
SELECT INTO newdir, newflip eswn[mydir], twist[mydir] FROM monkeymap WHERE id = myid;
IF newdir IS NULL THEN RAISE 'newdir cannot be null for id % and dir %', myid, mydir; end if;
/* If we hit a wall, stop walking and go to the rotation */
IF newdir = 0 THEN EXIT; END IF;
/* Move to the new location */
myid = newdir;
/* Set our new direction, as it might have changed by walking off the edge */
mydir = CASE WHEN newflip='>' THEN 1 WHEN newflip='v' THEN 2
WHEN newflip='<' THEN 3 ELSE 4 END;
/* Graphical output - see below
SELECT INTO myrec * FROM monkeymap WHERE id = myid;
PERFORM monkeydraw(myrec.z, myid, oldid, veryoldid); PERFORM pg_sleep(0.1);
veryoldid = oldid; oldid = myid;
*/
END LOOP;
/* Done walking, so time to rotate left or right */
IF spin[j] IS NULL THEN EXIT; END IF;
IF spin[j] = 'L' THEN
mydir = CASE WHEN mydir = 1 THEN 4 ELSE mydir-1 END;
ELSE
mydir = CASE WHEN mydir = 4 THEN 1 ELSE mydir+1 END;
END IF;
END LOOP;
/* Finished - display the final score */
RETURN (xy[2] * 1000) + (xy[1] * 4) + (mydir-1) FROM monkeymap WHERE id = myid;
END
$$;
SELECT monkey_inception();
-- Runs in 130ms !!
Running it produces the correct results in only 2s, as long as we force generic plans to run:
SET plan_cache_mode = force_generic_plan;
SELECT monkey_inception();
monkey_inception
------------------
34426
This was a hard one, mostly due to all the mental gymnastics of moving from 2-D to 3-D space and trying to get that represented correctly. Is this the last we will see of the monkeys? Stay tuned, we are close to the end.
AOC Day 22 - Bonus Round!
I built a paper cube, but it would also be nice to view how people move around the outside of the cube in real time. To that end, let's make some more ANSI graphics and have psql create some animated images! Our monkey_inception() function has these calls inside of it:
SELECT INTO myrec * FROM monkeymap WHERE id = myid;
PERFORM monkeydraw(myrec.z, myid, oldid, veryoldid); PERFORM pg_sleep(0.1);
veryoldid = oldid; oldid = myid;
When this is commented out, we grab the current face (myrec.z) and pass that, along with our current position, to a new function called monkeydraw. As this is meant to be us walking through a maze, followed by others, we also pass in the previous two positions, which allows us to simulate one leader and two followers moving along the outside of the cube. We sleep for 1/10 of a second, which controls how fast the animation appears.
The monkeydraw() function is detailed below. In short, it uses ANSI color codes to draw the current face of the cube, the current location as we are walking through it, and an indicator of which face is along each edge. The details are explained in the comments:
CREATE OR REPLACE FUNCTION monkeydraw(zz TEXT, myid INT, oldid INT, veryoldid INT)
RETURNS VOID language plpgsql AS $$
DECLARE
myrec RECORD; mytext TEXT = '';
resetcolor TEXT = E'\033[0m';
Acolor TEXT = E'\x1b[38;5;196m'; /* red */
Bcolor TEXT = E'\x1b[38;5;227m'; /* yellow */
Ccolor TEXT = E'\x1b[38;5;214m'; /* orange */
Dcolor TEXT = E'\x1b[38;5;225m'; /* pink */
Ecolor TEXT = E'\x1b[38;5;165m'; /* purple */
Fcolor TEXT = E'\x1b[38;5;21m'; /* blue */
Zcolor TEXT = E'\x1b[38;5;21m'; /* blue */
buffy TEXT = E'\x1b[38;5;196m'; /* red */
willow TEXT = E'\x1b[38;5;212m'; /* lightred */
xander TEXT = E'\x1b[37m'; /* white */
yellowbg TEXT = E'\x1b[48;5;227m';
topcolor TEXT; bottomcolor TEXT; leftcolor TEXT; rightcolor TEXT;
topname TEXT; bottomname TEXT; leftname TEXT; rightname TEXT;
BEGIN
Zcolor = CASE WHEN zz='A' THEN Acolor WHEN zz='B' THEN Bcolor
WHEN zz='C' THEN Ccolor WHEN zz='D' THEN Dcolor
WHEN zz='E' THEN Ecolor ELSE Fcolor END;
topcolor = CASE WHEN zz IN ('A','B') THEN Fcolor
WHEN zz IN ('C') THEN Acolor
WHEN zz IN ('D','E') THEN Ccolor ELSE Dcolor END;
topname = CASE WHEN zz='C' THEN 'A' WHEN zz IN('D','E') THEN 'C'
WHEN zz='F' THEN 'D' ELSE 'F' END;
bottomcolor = CASE WHEN zz IN ('A','B') THEN Ccolor
WHEN zz IN ('C') THEN Ecolor
WHEN zz IN ('D','E') THEN Fcolor ELSE Bcolor END;
bottomname = CASE WHEN zz='C' THEN 'C' WHEN zz IN('D','E') THEN 'F'
WHEN zz='F' THEN 'B' ELSE 'C' END;
leftcolor = CASE WHEN zz IN ('A','C','E') THEN Dcolor ELSE Acolor END;
leftname = CASE WHEN zz IN ('A','C','E') THEN 'D' ELSE 'A' END;
rightcolor = CASE WHEN zz IN ('A','C','E') THEN Bcolor ELSE Ecolor END;
rightname = CASE WHEN zz IN ('A','C','E') THEN 'B' ELSE 'E' END;
/* Draw the top border, showing the adjacent face's color and name */
mytext = format('%s%s%s%s%s%s', chr(10), topcolor, repeat(U&'\2588',25),
topname,repeat(U&'\2588',26), resetcolor);
/* Walk through each cell in the current face of the cube */
FOR myrec IN SELECT * FROM monkeymap WHERE z=zz ORDER BY y,x LOOP
/* If this is the first column, draw the left border first */
IF myrec.x=1 THEN
mytext = mytext || format('%s%s%s%s', chr(10), leftcolor,
CASE WHEN myrec.y=25 THEN leftname ELSE U&'\2588' END, resetcolor);
END IF;
/* If we are in the middle, show the name of the current face */
IF myrec.x = 25 AND myrec.y = 25 THEN
mytext = mytext || format('%s%s%s', yellowbg, zz, resetcolor);
/* If we are at the current location, show a red indicator */
ELSEIF myrec.id = myid THEN mytext = mytext
|| format('%s%s%s', buffy, U&'\2606', resetcolor);
/* Show our followers as well */
ELSEIF myrec.id = oldid THEN mytext = mytext
|| format('%s%s%s', willow, U&'\2606', resetcolor);
ELSEIF myrec.id = veryoldid THEN mytext = mytext
|| format('%s%s%s', xander, U&'\2606', resetcolor);
/* If this is an empty space, write out ...er... an empty space */
ELSEIF myrec.item = '.' THEN mytext = mytext || ' ';
/* This must be a block, so write it out in the current face's color */
ELSE mytext = mytext || format('%s%s%s', Zcolor, U&'\2588', resetcolor);
END IF;
/* IF this is the last column, draw the right border */
IF myrec.x=50 THEN
mytext = mytext || format('%s%s%s', rightcolor,
CASE WHEN myrec.y=25 THEN rightname ELSE U&'\2588' END, resetcolor);
END IF;
END LOOP;
/* Write the bottom border */
mytext = mytext || format('%s%s%s%s%s', chr(10), bottomcolor,
repeat(U&'\2588',25),bottomname,repeat(U&'\2588',26), resetcolor);
RAISE NOTICE '% %', chr(10), mytext;
END;
$$;
I decided against only showing a small part of the face, but went with the entire 50x50 grid. It makes the graphic a lot bigger, but the results are worth it:
