Re: Options to rowwise persist result of stable/immutable function with RECORD result - Mailing list pgsql-hackers

On Wed, Mar 22, 2023 at 4:32 PM Eske Rahn <eske@septima.dk> wrote:

Hi,

Thanks for the quick answer :-D

That was a nice sideeffect of lateral.

In the example, the calling code also gets simplified:

WITH x AS (
  SELECT clock_timestamp() rowstart, *, clock_timestamp() rowend FROM (
    SELECT '1' inp UNION
    SELECT '2'
  ) y,  LATERAL septima.foo(inp) g
)
SELECT * FROM x;

That solved the issue at hand, in a much better way. Thanks

Though I still fail to see why the other way should generally call the function for every column in the result record - if the function is STABLE or IMMUTABLE.

It gets rewritten to be effectively:

select func_call(...).col1, func_call(...).col2, func_call(...).col3

under the assumption that repeating the function call will be cheap and side-effect free.  It was never ideal but fixing that form of optimization was harder than implementing LATERAL where the multi-column result has a natural output in the form of a multi-column table.  A normal function call in the target list really means "return a single value" which is at odds with writing .* after it.