Array operators as conditions

[pi-nathan]

Postgres supports a few array operators that I think would fit as conditions: https://www.postgresql.org/docs/16/functions-array.html

Specifically the @>, <@, and && operators. I'm not entirely certain if it's possible to typecheck that the left-hand column is an array, but the actual implementation should be fairly straightforward? Something similar to this?

export const arrayContains = <T>(a: readonly T[]) => a.length > 0 ? sql<SQL, boolean | null, T>`${self} @> (${vals(a)})` : sql`false`;
export const arrayIsContained = <T>(a: readonly T[]) => a.length > 0 ? sql<SQL, boolean | null, T>`${self} <@ (${vals(a)})` : sql`false`;
export const arrayOverlaps = <T>(a: readonly T[]) => a.length > 0 ? sql<SQL, boolean | null, T>`${self} && (${vals(a)})` : sql`false`;

That syntax is probably slightly off. I'll probably end up making these for myself and can happily put up a PR once I have them functional.

[pi-nathan]

This seems to work? At least in my very limited prototyping.

export const arrayContains = <T>(a: readonly T[]) =>
    a.length > 0 ? sql<SQL, boolean | null, T[]>`${self} @> ARRAY[${vals(a)}]` : sql`false`;
export const arrayIsContained = <T>(a: readonly T[]) =>
    a.length > 0 ? sql<SQL, boolean | null, T[]>`${self} <@ ARRAY[${vals(a)}]` : sql`false`;
export const arrayOverlaps = <T>(a: readonly T[]) =>
    a.length > 0 ? sql<SQL, boolean | null, T[]>`${self} && ARRAY[${vals(a)}]` : sql`false`;