On Wed, May 10, 2023 at 9:49 AM Andrew Gierth <andrew@xxxxxxxxxxxxxxxxxxxx> 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...
