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. >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(...)) >> 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. David J. -- View this message in context: http://postgresql.nabble.com/Array-Comparison-tp5829471p5829473.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
