On Thu, Feb 9, 2023 at 9:09 AM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:
On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne <ddevienne@xxxxxxxxx> wrote:On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne <ddevienne@xxxxxxxxx> 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, --DDAlso, 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.
