|
Fra: David G. Johnston <david.g.johnston@xxxxxxxxx>
Sendt: 3. februar 2021 16:08 >On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen <NJN@xxxxxx> 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.
> |
