Fra: David G. Johnston <david.g.johnston@gmail.com>
Sendt: 3. februar 2021 16:08
>On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen <NJN@dst.dk> wrote:
>Hello all
>
>I have som data in a resultset. E.g:
>
>id date_begin date_end amount
>1 2021-01-04 2021-02-06 100
>2 2021-03-17 2021-05-11 234
>
>I have a table returning function that can take one row and split it into constituent monthpieces and distribute amount proportionally.
>
>select * from func(1, 2021-01-04, 2021-02-06, 100);
>[...]
>How can I accomplish this, please.
>
>Lateral Join.
>
>(not tested)
>SELECT *
>FROM resultset, func(id, date_begin, date_end, amount);
>
>David J.
Of course, yes. It works. My Oracle background isn't very helpful when it comes to including lateral joins in my thinking.
with res(id, date_begin, date_end, amount) as (select *
from (values (1::bigint, '2021-01-04'::date, '2021-02-06'::date, 100::numeric),
(2::bigint, '2021-03-17'::date, '2021-05-11'::date, 234::numeric)) a)
select r.id, m.date_start, m.date_end, m.amount
from res r,
month_parts_low_freq(r.id, r.date_begin, r.date_end, r.amount) m;
produces the expected result. Now I will see how it goes with about 150 million rows in input.
>