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

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

> On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne <ddevienne(at)gmail(dot)com>
> wrote:
>
>> On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Now we'd like to do the same for composite keys, and I don't know how
>>>> to do that.
>>>>
>>>
>>> An array-of-composites is simply:
>>> SELECT ARRAY[ ROW(1, 'one'), ROW(2, 'two') ]::composite_type[];
>>>
>>
>> Thanks. I don't consider that "simple" myself :). But I'm definitely not
>> an advanced PostgreSQL user!
>> Would still appreciate a more fleshed out demo, if anyone is kind enough
>> to provide it. Thanks, --DD
>>
>
> Also, I'm still not sure how to write that WHERE clause, with the $1 being
> an array of a composite type.
> And since this is a binary bind, what kind of OIDs to use. Your example
> seems to generate a type on-the-fly for example David.
> Will we need to create custom types just so we have OIDs we can use to
> assemble the bytes of the array-of-composite bind?
> I believe there's an implicit ROW type per table created. Are there also
> implicit types for composite PKs and/or UNIQUE constraints?
> Lots of questions...
>

postgres=# select (1, 'one'::text) = any(array[(1,
'one'::text)::record]::record[]);
?column?
----------
t
(1 row)

Not sure how much that helps but there it is.

If you wanted to use an actual explicit composite type with an OID it would
need to be created.

There where clause is the easy part, its the code side for setting the
parameter that I don't know. Ideally the library lets you pass around
language-specific objects and it does it for you.

You could consider writing out a JSONB object and writing your condition in
terms of json operators/expressions.

David J.