Subject: scalar plpgsql functions and their stability flags

From: Victor Dobrovolsky <[email-masked]>
Date: Sun, 26 May 2024 16:20:10 +0300

Good day experts...

Question on scalar plpgsql functions stability flags (immutable, stable)
regarding how it works in sql queries.

It is clear that for immutable/stable functions with constant parameters,
 query planner could/should calculate value in a parse time and use it
directly in query, or at least once per query.

But it is unclear for me what exactly should/can happens when parameters
are bounded not to constant values but to query fields.
In such a case there could be some caching mechanics involved for
parameters combinations and result values.
Like building a hash table for that or something similar.

Can someone give me guidance on this matter.
What limits the usefulness of such a mechanism, if it exists.

Thank you.

---

From: Tom Lane <[email-masked]>
Date: Sun, 26 May 2024 10:27:14 -0400

> directly in query, or at least once per query.

Immutable, yes, stable, no.

Awhile back there was a draft patch to cache outputs of stable functions
after running them once in a query, but I don't think anyone's still
working on that.  IIRC we were having a hard time convincing ourselves
that the extra bookkeeping would pay for itself.

> Like building a hash table for that or something similar.

No such mechanism exists.  Again, there would be a lot of tradeoffs
involved and it's difficult to say if it'd be a win.

			regards, tom lane