The available options:
Nevertheless I had an idea, but for this I had to wait until the poll closes.
My idea wouldn’t have been easy with every of the available options, but the final poll result of
16 made it relatively easy. This answer is the result if you ignore (forget, blame your math teacher) the order of operations in mathematics. But this blog posting and this answer would not be much fun. Instead I use PostgreSQL to bend my answer to the result of the poll.
How to approach this
There are a couple options, like working on the order of operations. But this is already some work. This specific example works also if you multiply
2, instead of adding them: 2 * 2 * 4 = 16. All I have to do is overload the
+ operator to multiply instead. After all, it’s just bending the database to the expected result.
Adding the new operator
First I need to find the correct operator definition in the catalog. PostgreSQL interprets the
4 as integers, therefore I need the
+ operator for where the left and right argument is an integer:
As we can see, the actual function doing this operation is
int4pl. Let’s look at the operator for
The function used here is
So how about just defining a new operator for
+, but using
There are two ways to do this: I’m defining a new operator, which lives in the public schema (or any other schema I specify). I could also just update the operator in
pg_catalog. And I’m using a separate database: in case I screw up the catalog, I can just drop everything and start over. Don’t do this in your default or production database!
The search_path problem
By default, the functions in
pg_catalog come first. If you change
search_path, and don’t include
pg_catalog is always placed first. However if you also include
search_path, then the functions and operators are looked up in the order you have schemas specified in
search_path. I explained this in more details in this blog post here.
What I have to do is:
Then I can run my query:
Under the hood, PostgreSQL tries to find operators for each operation. The
* comes from
pg_catalog (second in
+ comes from
public because that’s the first hit in
The problem is a bit more real, because one can do this:
Now the operator I created (and any other operator in
public) overloads the operators in
pg_catalog. PostgreSQL fixes this problem with public by updating the permissions for the public schema in version 15 of the database. If people have write access to the DB, they can still overload even simple operators like
* and then return unexpected results for everyone. Or return expected but slightly false results.