Immutable dynamic sql functions

Question

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.

Answer 1

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:

• Difference between language sql and language plpgsql in PostgreSQL functions

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:

• Does PostgreSQL support "accent insensitive" collations?

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