Re: Does it make sense to break a large query into separate functions?

On Wed, May 8, 2013 at 2:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> odd that stable function is inlined but immutable isn't!
>
> Well, it knows that the expansion to to_date() would only be stable not
> immutable (because to_date depends on some GUC settings), so doing the
> expansion could change the behavior, eg by preventing constant-folding.

I see your point-- but you have to admin it's just plain weird -- in
this example the behavior is in fact immutable and marking it as such
causes it to not be inlined. For purposes of inlining, regardless of
the implementation, IMO the function decoration should trump forensic
analysis of the function body. Translation: immutable and stable
functions should *always* be inlined.

More oddness -- when I wrap, say, random() with stable function, I get
unique value per returned row, but same value across the set when
wrapped with immutable.

> Although usually wrapping a stable function in an immutable one is a
> recipe for disaster, we don't forbid it because there are cases where it
> makes sense --- for instance, you might know that the function really is
> immutable *in your usage*, and want to use it as an index function or
> some such. But the SQL-function wrapper adds a lot of overhead. I
> think a plpgsql wrapper would be better here, if you need to cheat about
> the mutability.

Right. In this case, plpgsql is only about 10% faster than
non-inlined sql. inlined sql completely smokes both of them.
Regardless, this is a scratch example off of the top of my head. I'm
curious if there's a good reference for inlining rules and if their
limits have been well explored (and if so, so be it). What I
ultimately want is a way to abstract code without using views, dynamic
sql, etc.

merlin