On Wed, Mar 22, 2023 at 4:46 PM David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Wed, Mar 22, 2023 at 4:32 PM Eske Rahn <e...@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. > > Actually, it is less "optimization" and more "SQL is strongly typed and all columns must be defined during query compilation". David J.
