Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:

> Hi. We are implementing an API which takes a list of row keys, and must
> return info about those rows. To implement that efficiently, in as few
> round-trips as possible, we bind a (binary) array of keys (ints, uuids, or
> strings) and that works great, but only if the key is a scalar one.
>
> Now we'd like to do the same for composite keys, and I don't know how to
> do that.
> Is it possible? Could someone please help out or demo such a thing?
> We are doing it in C++ using libpq, but a pure SQL or PL/pgSQL demo would
> still help (I think).
>
>
It's trivial in pl/pgsql since I don't have to deal with serialization of
the data.

An array-of-composites is simply:

SELECT ARRAY[ ROW(1, 'one'), ROW(2, 'two') ]::composite_type[];

Not sure about the binary part but there are rules for how to serialize to
text both composites and arrays, and quite probably libpq provides
functions for this already though i've never used it raw.

David J.