0

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.

Improve this question
4
  • If it's (really) immutable, then it's immutable regardless of its code. But a function that accesses the database can never be immutable.
    – user1822
    Jan 20, 2022 at 7:30
  • Yes, that's what I thought, simply wanted to confirm. So, anyelement/anyarray inputs also don't matter. Since with any element I may send '1' and 1 but have the same result. I am assuming it will just save for each input separately what result it got.
    – Chessbrain
    Jan 20, 2022 at 8:04
  • What's the actual question here? Can you clarify?
    – Erwin Brandstetter
    Jan 20, 2022 at 16:35
  • @ErwinBrandstetter Basically, does it matter at all what kind of code is inside, and the type of input for a function when declaring it as immutable? (both scenarios don't call any tables)
    – Chessbrain
    Jan 21, 2022 at 7:47

1 Answer 1

Reset to default
2

If a function is declared IMMUTABLE, but the body contains functions of lesser volatility (STABLE, VOLATILE), then function inlining of SQL functions is disabled. So, yes, Postgres does care "what's inside".

There are differences between SQL functions and functions with other procedural languages like PL/pgSQL. Dynamic SQL is not possible in SQL functions to begin with. Related:

But for many purposes, Postgres is satisfied with the declared function volatility. For example, if you build an index involving a custom function, it must be declared IMMUTABLE. It is your responsibility to keep that promise, or the index will break silently.

Example:

Polymorphism (using anyelement et al.) is an orthogonal concept. Polymorphic functions can have any volatility declaration.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.