Support for the `ANY` operator?

[intractable]

Thanks for the fantastic library, I have been enjoying using it to adapt a bunch of code from handrolled SQL via postgresql-simple.

I have a SQL query that does something like WHERE a = ANY(as) where as is an array (uuid[] in this case) and a is an element, but I've not been able to figure out how to represent that in rel8 (or if it's possible at present).

E.g.,

let a :: Expr a = ... 
row@MyRow{as} <- someQuery -- as :: Expr (Vector a)
as <$ where_ (a `in_` as)

I obviously can't do that, as the types are wrong for in_, so I think I either need some way of writing a ==. any_ as (ideally that lowers to actual use of ANY, I suppose), or I need to do the equivalent thing via some other means.

Any insight is appreciated; I'm likely missing something obvious.

[ocharles]

As per https://github.com/circuithub/rel8/issues/188#issuecomment-1172500747 I think you might be ok with

any_ :: Expr [a] -> Expr a
any_ = dbFunction "any"

Then you would write

where_ $ uuid ==. any_ uuids

[intractable]

any_ :: Expr [a] -> Expr a
any_ = function "any"

Unfortunately, that doesn't seem to work (and I'm not familiar enough with the instances yet to grok what it's complaining about yet), at least not in 1.3.1.0:

• No instance for (DBType
(rel8-1.3.1.0:Rel8.Schema.Null.Unnullify'
(rel8-1.3.1.0:Rel8.Schema.Null.IsMaybe a) a))
arising from a use of ‘function’
• In the expression: function "any"
In an equation for ‘any_’: any_ = function "any"

I'll give it a shot on 1.4.0.0 just in case.

[ocharles]

Try sticking a DBType a => at the start?

[intractable]

That worked, but it looks like the generated SQL is invalid; I believe because how the cast is being applied? An expression like u ==. any_ us renders as

((CAST(E'94692290-05a2-2f1f-9e26-f31cea05c7c2' AS uuid)) = (CAST(ANY(\"us1_15\") AS uuid)))

and the captured error complains about the use of ANY:

ResultError (ServerError "42601" "syntax error at or near \"ANY\"" Nothing Nothing)

[ocharles]

Ah, that's no good. We may have to add this in then, thanks for testing!

[intractable]

I'm probably not familiar enough with the codebase to contribute much yet, but if you're inclined to sketch what you think is needed at a high level, I'd be happy to take a stab at it. Maybe an annotation of some kind for constructs like this which force the cast to be omitted?

[shane-circuithub]

For what it's worth @intractable, here's a snippet from CircuitHub's codebase that provides a function elem :: Sql DBEq a => Expr a -> Expr [a] -> Expr Bool which like it would do what you're looking for:

(<@) :: Sql DBEq a => Expr [a] -> Expr [a] -> Expr Bool
(<@) = binaryOperator "<@"

elem :: Sql DBEq a => Expr a -> Expr [a] -> Expr Bool
elem = (<@) . (id $*) . listTable . pure

We should probably move this into Rel8 proper at some point.

[intractable]

@shane-circuithub Somehow I had missed this, thanks very much for the example!