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 e...@septima.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.g.johns...@gmail.com> wrote: > On Tuesday, March 21, 2023, Eske Rahn 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. > >
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.g.johns...@gmail.com> wrote: > On Wed, Mar 22, 2023 at 4:32 PM Eske Rahn 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.
Re: Options to rowwise persist result of stable/immutable function with RECORD result
On Wed, Mar 22, 2023 at 4:32 PM Eske Rahn 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. David J.
Re: Options to rowwise persist result of stable/immutable function with RECORD result
On Tuesday, March 21, 2023, Eske Rahn 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.
