Re: Options to rowwise persist result of stable/immutable function with RECORD result
| От | Eske Rahn |
|---|---|
| Тема | Re: Options to rowwise persist result of stable/immutable function with RECORD result |
| Дата | |
| Msg-id | CAMVrTS7FsrQ=Ngzt8OXBMYOTpRNGC1nRNRdRD1MwR4SYE67vww@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Options to rowwise persist result of stable/immutable function with RECORD result ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-hackers |
Hi,
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;
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)
Eske Rahn
Seniorkonsulent
+45 93 87 96 30
--------------------------
Septima P/S
Frederiksberggade 19, 2. sal
1459 København K
+45 72 30 06 72
On Wed, Mar 22, 2023 at 10:50 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
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.David J.
В списке pgsql-hackers по дате отправления: