Have to admit, on first glance this challenge looks a bit complicated. It’s well suited for languages which can do string manipulations, but it’s not well suited for PostgreSQL. Earlier today I already looked at this problem together with the kid, in Python. Therefore I already knew that I have to jump multiple rows as well. To sum it up: adjust x, including overruns, jump multiple rows in y direction, count trees along the way. All in a single SQL query.
Decided to do the map search in a pl/pgSQL instead, and write a function for it.
Task 1: you get a map of “.
” and “#
”, where the “#
” are trees. You get instructions to move a certain number of steps into x
and y
direction, and see if there is a tree. Then repeat until the end of the map. A detail problem is that the number of fields in x
direction is smaller than the y
direction. There are no clear instructions how to handle this, but the correct solution is to just start at the leftmost position again.
Task 2: Repeat the task from task 1, but 5
times with 5
different instructions for x
and y
movement. The resulting numbers are to be multiplied.
Preparation
As usual, I load the data into a table in PostgreSQL. This time I not only have a column with the TEXT data (the forst), but I also need an ID which I use to query specific rows later on. In order to do that I add a SERIAL column to the table.


And load the data:


A total of 323
rows in the table:


There is however a small problem: since I want to access the rows by ID, I need to make sure that the IDs are strictly monotonous, and start with 1
. The 1
I ensure by creating the table from scratch, this also creates a fresh sequence for the ID. Let’s check if the IDs are monotonous:


No divergence, good.
And I need to know the total length of the rows:


Every row is 31
characters. I will encode that into the pl/pgSQL function later.
Task 1
Since I already know that I have to jump different rows and columns, I create he function in a way which can handle both x
and y
. A pl/pgSQL function has a header, which defines the name, input types (if any), and the return type. And at the end the used language is specified.


A pl/pgSQL function can have an optional DECLARE block to define variables used in the function:


numrow
: holds the current row number, starts with0
numcolumn
: holds the current column number, starts with0
numtrees
: number of trees found, starts with0
lengthrow
: the length of each text row, for easyness I looked this up before and hardcode it to31
herenumrow
s: number of rows in the tablethisrow
: aTEXT
variable used to fetch the row I’m working on in the function
The first task is to find out how many rows the field has. I could have done that before as well, but I thought I leave this in the function as an exercise:


This query populates numrows with the number of rows in the table. The query is static, as in: can’t be changed in the function. And the “SELECT … INTO” form is not to be confused with the same syntax in regular SQL. In a pl/pgSQL function this will store the result into the variable after INTO
.
Next I need to loop over the field. For that I already know that I might have to jump multiple lines, therefore a simple loop over each line is not sufficient. Hence I also need to know the number of rows before: this way I can have a while loop which runs as long as numrow is smaller than numrows.


Inside the while loop I first increase the line number by the number of y
steps:


And then I fetch the row from the table:


Strictly speaking I don’t need to fetch the entire row: I could combine this step with the next step and just fetch the one character I’m interested in. But it’s cleaner this way.
In the next step I move the number of x
steps sideways. And if there is an overrun, I just take the remaining number of steps and start again with them:


Finally now that I have the line, and know which position in the line I can select the character and see if that is a tree:


At the end return the number of trees:


All the function code in one piece:


Now I have one function which can move arbitrary steps into x
and y
direction. All what is to do to solve task one is to execute the function:


Task 2
Repeat this 5
times with 5
different walking instructions, and multiply the resulting number of trees:

