Re: Returning multiple rows in materialized mode inside the extension

On Wed, Mar 30, 2022 at 9:01 AM Piotr Styczyński <piotr(at)styczynski(dot)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
LATERAL func_call(arr_input.arr)
;

David J.