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,

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 
--------------------------
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 по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: Reconcile stats in find_tabstat_entry() and get rid of PgStat_BackendFunctionEntry
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Options to rowwise persist result of stable/immutable function with RECORD result