Immutable dynamic sql functions



  • 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.



  • If a function is declared IMMUTABLE, but the body contains functions of lesser volatility (STABLE, VOLATILE), then https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions 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:

    • https://stackoverflow.com/a/24771561/939860

    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:

    • https://stackoverflow.com/a/11007216/939860

    https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-POLYMORPHIC-FUNCTIONS (using anyelement et al.) is an orthogonal concept. Polymorphic functions can have any volatility declaration.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2