Scenario #1
Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it actually truly be immutable? a.k.a will the compiler/query processor be aware of this specific scenario?
According to the official documentation
An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments.
By this logic, it should work just fine as it only cares about the input being the same.
Scenario #2
Same as above, but the input type is anyelement
or anyarray
.
Considering that SQL is a strongly typed language (from official docs)
SQL is a strongly typed language. That is, every data item has an associated data type which determines its behavior and allowed usage.
And knowing that anyelement
is simply a pseudo type because in fact during processing of functions it knows it's (for example) an integer or string. So from that point of view it shouldn't matter if a function accepts anyelement
when it's immutable as well.
I would like to know if the written above is indeed the true behavior of PostgreSQL under those conditions. And if there's any difference compared to regular immutable functions with regular SQL.
'1'
and1
but have the same result. I am assuming it will just save for each input separately what result it got.