On Wed, Mar 30, 2022 at 9:01 AM Piotr Styczyński <piotr@styczynski.in> wrote:
I don’t know if this mailing list is a good place to ask this question, but if it’s not, just correct me.
pgsql-general is probably better
The problem:
We currently have a one-to-many function (an operation that produces multiple rows per one one input row).
Now we would like to translate that functionality to a sensible many-to-many.
This seems like a big gap.
Input Situation Rows:
1
2
3
What is the expected output
1 A
1 B
1 C
2 A
2 B
2 C
3 A
3 B
3 C
I really don't know how you would change the internals to handle this - I'm doubting it would even be possible. If asked to accomplish this using just standard PostgreSQL I would turn the inputs into an array
{1,2,3}
and pass that array into a set-returning function. Now I have:
{1,2,3} A
{1,2,3} B
{1,2,3} C
as an output, and I can just unnest the array column to produce the final result.
Something like (not tested):
SELECT unnest(arr_input.arr), func_call
FROM
(SELECT array_agg(inputvals) AS arr FROM tbl) AS arr_input
