Обсуждение: table returning function for each row in other resultset
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); returns 1,2021-01-04,2021-01-31,84.848485 1,2021-02-01,2021-02-06,18.181818 So far, so good. Now, what I want is to run the function on the resultset from above and get 1,2021-01-04,2021-01-31,84.848485 1,2021-02-01,2021-02-06,18.181818 2,2021-03-17,2021-03-31,63.818182 2,2021-04-01,2021-04-30,127.636364 2,2021-05-01,2021-05-11,46.8 How can I accomplish this, please. Regards Niels Jespersen
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.
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.
>