Postgres Pro
Downloads
Home   >   mailing lists

Re: WHERE col = ANY($1) extended to 2 or more columns? - Mailing list pgsql-general

From Dominique Devienne
Subject Re: WHERE col = ANY($1) extended to 2 or more columns?
Date
Msg-id CAFCRh-_haa=ZrgMWEFmppqDHaKE1nO3=VqUfXK+OAZPo+9VO-w@mail.gmail.com
Whole thread Raw
In response to Re: WHERE col = ANY($1) extended to 2 or more columns?  (Dominique Devienne <ddevienne@gmail.com>)
Responses Re: WHERE col = ANY($1) extended to 2 or more columns?
List pgsql-general
Tree view
On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne <ddevienne@gmail.com> wrote:
On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne <ddevienne@gmail.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...

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Using PostgreSQL for service discovery and health-check
Next
From: Adrian Klaver
Date:
Subject: Re: Concurrency issue with DROP INDEX CONCURRENTLY