From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | andrew(at)tao11(dot)riddles(dot)org(dot)uk |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Return rows in input array's order? |
Date: | 2023-05-10 10:02:41 |
Message-ID: | CAFCRh-8qneCBe4p7k0zpbCMgXy1aE3-Mq6iJNiMWZ1=o+KDiKw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, May 10, 2023 at 9:49 AM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:
> Dominique> Is it possible to maintain $1's order directly in SQL?
>
> This is the correct way:
>
> SELECT ... FROM unnest($1) WITH ORDINALITY AS u(id,ord)
> JOIN yourtable t ON t.id=u.id
> ORDER BY u.ord;
>
Thanks Andrew, for spelling it out for me. Appreciated.
Also thanks to others who chimed in.
I assume that if the PK is composite, and I pass the PK tuples as separate
same-cardinality "parallel" arrays, I can "zip" those arrays back via a
multi-join
using their ordinals before joining with the composite-PK table? --DD
PS: I guess the ideal plan depends both on the table itself, but also the
cardinality
of the array(s) passed in as bind variable(s) at runtime to the prepared
statement, right?
But from past posts, I got the impression the plan of a prepared
statement is "fixed",
and does not depend on "bind peeking" like it can in Oracle, to take
those bound
array's cardinality into account at PQexecPrepared-time?
PPS: This is something I actually failed to do in Oracle in the past...
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2023-05-10 11:08:45 | Re: Return rows in input array's order? |
Previous Message | Andrew Gierth | 2023-05-10 07:49:16 | Re: Return rows in input array's order? |