Re: Options to rowwise persist result of stable/immutable function with RECORD result

On Wed, Mar 22, 2023 at 4:46 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Mar 22, 2023 at 4:32 PM Eske Rahn <eske(at)septima(dot)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.
>
>
Actually, it is less "optimization" and more "SQL is strongly typed and all
columns must be defined during query compilation".

David J.