On Friday, December 5, 2014, Ian Harding <harding.ian@xxxxxxxxx> wrote:
On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <david.g.johnston@xxxxxxxxx> wrote:Ian Harding wrote
> On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <
> harding.ian@
> > 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.
