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)
On Tuesday, March 21, 2023, Eske Rahn <eske@septima.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.
