table returning function for each row in other resultset

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.

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.