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
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
y movement. The resulting numbers are to be multiplied.
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 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.
Since I already know that I have to jump different rows and columns, I create he function in a way which can handle both
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 with
numcolumn: holds the current column number, starts with
numtrees: number of trees found, starts with
lengthrow: the length of each text row, for easyness I looked this up before and hardcode it to
numrows: number of rows in the table
TEXTvariable 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
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
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
y direction. All what is to do to solve task one is to execute the function:
5 times with
5 different walking instructions, and multiply the resulting number of trees: