Fwd: Array Comparison - Mailing list pgsql-general

On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <david.g.johnston@gmail.com> wrote:

Ian Harding wrote
> On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding &lt;

> harding.ian@

> &gt; wrote:
>> I have a function that returns bigint[] and would like to be able to
>> compare a bigint to the result.

Here are some of your options:

http://www.postgresql.org/docs/9.3/interactive/functions-array.html
http://www.postgresql.org/docs/9.3/interactive/functions-comparisons.html

The direct type-to-type operators are covered in the first link while
generic comparison mechanisms - including those the can compare arrays to
scalars - are in the second one.

There are lots of ways to compare things; e.g., are they equal, is one
greater than another and, for multi-valued items, does one contain the other
or do they overlap


>> select 935::bigint in (select
>> fn_descendents('trip'::varchar,61::bigint));
>> ERROR:  operator does not exist: bigint = bigint[]

As shown by the error the application of "IN" simply checks to see if any of
the ROWS of the given select match against the left-hand value.  That means
zero or more evaluations of:
bigint = bigint[]
which does not makes sense.  There is no special evalulation mode for a
subquery that only happens to return a single row.

Ah.  Right.  That makes sense. 
 

From the second link above you can express the scalar-to-array comparison
you seek through the use of "ANY".

bigint = ANY(bigint[])

Since your function already returns an array you do not to (and indeed
cannot) use a subquery/SELECT.  Simply write:

935::bigint = ANY(fn_descendents(...))

I sort of figured that out.... only I fatfingered it to "... IN ANY(..." 
 

>> Hmmm.. This works...
>>
> select array[935::bigint] <@ (select
> fn_descendents('trip'::varchar,61::bigint));
>
> Still, why?

Do you understand the concept of array containment - what it means for an
array to contain or be contained by another array?  The documentation
assumes that concept is known and simply provides the syntax/operators
needed to access it.

 

Ah, but isn't this the same "There is no special evalulation mode for a subquery that only happens to return a single row." from above?  I'm asking "Is this scalar array contained in the result set of this select for which there is no special evaluation mode for the happy coincidence that it only has one value?  That's what surprised me.