Thread: arrays and subselects

Sorry to repeat myself, but I've gotten a couple improperly formed
digests which my mail reader couldn't decipher, and I'm afraid I
missed a response.

Am I supposed to be able to use the result of selecting an array as
the subquery for an 'in' clause?  It seems very natural, and would
be mighty convenient.

For example:

create table testme1 (
key1   int2,
data1  int2[3]
);

create table testme2 (
key2   int2,
data2  int2
);

insert into testme1 values(1,'{1,2,3}');
insert into testme1 values(2,'{4,5,6}');
insert into testme2 values(10,5);

select key2 from testme2 where data2 in (select data1 from testme1
where key1 = 2);

Generates the following error

ERROR:  Unable to identify an operator '=' for types 'int2' and '_int2'
    You will have to retype this query using an explicit cast

Although I could name the array elements individually, this would be
most useful when the length of the array is variable.

I'd greatly appreciate any tips, pointers, or insight.

Thanks,
Dave

On Wed, 20 Sep 2000, Dave Hollenbeck wrote:

> insert into testme1 values(1,'{1,2,3}');
> insert into testme1 values(2,'{4,5,6}');
> insert into testme2 values(10,5);
>
> select key2 from testme2 where data2 in (select data1 from testme1
> where key1 = 2);
>
> Generates the following error
>
> ERROR:  Unable to identify an operator '=' for types 'int2' and '_int2'
>     You will have to retype this query using an explicit cast
>
> Although I could name the array elements individually, this would be
> most useful when the length of the array is variable.

I'd suggest looking at the array package in contrib which includes
functions/operators for element-in-array.  Then you can use a simple
join or exists to do the query (also avoiding potential ugliness in using
in anyway).

The other option of course to make an int2=_int2 operator which is the
element-in-array, which would probably let you use the above syntax.
However, I'm not sure that's a good default.  This is a problem with
arrays, are they data or are they a short form for rows of that value
type?