Re: Returning multiple rows in materialized mode inside the extension
| От | David G. Johnston |
|---|---|
| Тема | Re: Returning multiple rows in materialized mode inside the extension |
| Дата | |
| Msg-id | CAKFQuwa=-RhN+AU8u7s0X3Lmz+D0Sd84mCUtOgpEOQzLfW5CYQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Returning multiple rows in materialized mode inside the extension (Piotr Styczyński <piotr@styczynski.in>) |
| Список | pgsql-hackers |
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
LATERAL func_call(arr_input.arr)
;
David J.
В списке pgsql-hackers по дате отправления: