Fwd: Array Comparison

Please send replies to the list.

On Friday, December 5, 2014, Ian Harding <harding(dot)ian(at)gmail(dot)com
<javascript:_e(%7B%7D,'cvml','harding(dot)ian(at)gmail(dot)com');>> wrote:

>
>
> On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)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.
>
>
No. Your query will output one row for every input row the subquery
generates - each row having a true or false value depending on whether the
particular value contains your array constant.

Your initial attempt incorrectly tried to get in the IN to apply to each
element of the only array that was returned but that doesn't work and which
is why the scalar=array comparison failed; the array is never decomposed.
If your replace <@ with IN in this example you would get a single result
(Boolean false in this case) regardless of how many rows the subquery
returns. The IN wraps the subquery expression and makes it into a kind of
scalar while directly using the operator against the subquery causes
multiple evaluations.

See: SELECT generate_series(1,10) - for an idea of how row generating
expressions in the select list behave.

David J.