Re: array support patch phase 1 patch

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Also I didn't put in the bool_op stuff.  That seemed pretty messy; in
>> particular I didn't care for looking at the operator names to decide
>> what to do.  Another problem is that the actual lookup of the scalar
>> operators would be schema search path dependent.  I'd feel more
>> comfortable with something that created a tighter binding of the array
>> operators to the underlying scalar operators.  Not sure how to do it,
>> though.

> But the lookup would be schema search path dependent if we were given
> two scalars, so I don't see this as any different.

But it is different, because the lookup happens at run time not at parse
time.  In particular consider rules or views that might be executed with
current search paths completely unrelated to what was used when they
were defined.  You've now got a situation where the rule/view author
does not have control of what code is being executed on his behalf.
Not good.

(Come to think of it, much the same complaint might be laid for PL and
SQL functions; maybe it'd be better to associate a schema search path
with a function when it's created?)

Perhaps we could insist that the relevant scalar operators be found in
the same schema the array operator lives in?  Safe but restrictive...

> Would it be better to
> use the same operators as the scalars ("=", "<>", ...etc)? It makes
> sense to me that "array = element" should apply the "=" operator for the
> element data type, across all of the array elements.

It seems a little surprising to me.  "IN" and "=" mean quite different
things, and I'd not expect them to be represented by the same operator name.

> Maybe this takes us
> back to Peter's suggestion:
>    expression IN (array)
>    expression NOT IN (array)
>    expression operator ANY (array)
>    expression operator SOME (array)
>    (expression) operator (array)
>    (expression) operator ALL (array)

There's a lot to be said for that, if we can think of a way to do it.
I believe this way would force us to integrate the operations into the
parser, though, rather than just throwing a few polymorphic functions
at the problem.  It's probably a lot more work :-(

> OK. I'll look at these issues again. Should I also look to implement:
>    array <> array
>    array > array
>    array < array
>    array >= array
>    array <= array

> as Hannu suggested?

Not sure about that.  A point that should have been made in that
conversation is that SQL already defines "row comparison" operations
that are supposed to act in much the same fashion Hannu suggested.
We don't currently implement those correctly, but we should.

Consider also that the minute we have the above functions, people will
expect to be able to build indexes on array columns and sort by array
columns.  The bool_ops code as it stands can't effectively support that,
because these contexts do not have expression parsetrees hanging around
to pass to fn_expr.

            regards, tom lane