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

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.

BUT as I can not think up a sensible example where LATERAL will *not* do
the trick, so the oddity becomes academic.
So just a thing to remember: *always use lateral with functions with record
result types* - unless they are volatile)

Med venlig hilsen
*Eske Rahn*
Seniorkonsulent
+45 93 87 96 30
eske(at)septima(dot)dk
--------------------------
Septima P/S
Frederiksberggade 19, 2. sal
1459 København K
+45 72 30 06 72
https://septima.dk

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

> On Tuesday, March 21, 2023, Eske Rahn <eske(at)septima(dot)dk> wrote:
>
>> Hi,
>>
>> I have noticed a rather odd behaviour that is not strictly a bug, but is
>> unexpected.
>>
>> It is when a immutable (or stable) PG function is returning results in a
>> record structure a select on these calls the function repeatedly for each
>> element in the output record.
>>
>
> The LATERAL join modifier exists to handle this kind of situation.
>
> David J.
>
>