From: | Niels Jespersen <NJN(at)dst(dot)dk> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | SV: table returning function for each row in other resultset |
Date: | 2021-02-04 05:31:12 |
Message-ID: | 818de3ff1a9c4e0cbc2f83fb539b5fe9@dst.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Fra: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sendt: 3. februar 2021 16:08
>On Wed, Feb 3, 2021 at 8:01 AM Niels Jespersen <NJN(at)dst(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adith Suresh | 2021-02-04 09:46:02 | PgAdmin 4 GUI not responding |
Previous Message | Sumit Raja | 2021-02-04 05:24:59 | pg_trgm for address search |