Re: Array Contained By Array Question

On Fri, Jun 10, 2011 at 3:38 PM, Arthur M. Kang <arthurmkang@gmail.com> wrote:
> Merlin,
>
> Thanks so much for the reply.  I was suspecting that this was the way to get
> this done.
>
> Thanks again for the help!
>
> Arthur
>
> On 6/10/2011 10:31 AM, Merlin Moncure wrote:
>>
>> On Fri, Jun 10, 2011 at 11:00 AM, Arthur M. Kang<arthurmkang@gmail.com>
>>  wrote:
>>>
>>> How can I search a multidimensional array for an EXACT "subarray" match?
>>>
>>> The following produces true when I want it to produce false.  Obviously,
>>> I
>>> would only want it to produce true for ARRAY[1,2], ARRAY[3,4], or
>>> ARRAY[5,6].
>>>
>>> SELECT ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]] @>  ARRAY[ARRAY[1,6]];
>>>
>>> Any help is greatly appreciated.
>>
>> create or replace function slice_in_array(needle anyarray, haystack
>> anyarray) returns bool as
>> $$
>>   select $1 in (select $2[v:v] from generate_series(1, array_upper($2, 1))
>> v);
>> $$ language sql immutable;
>>
>> select slice_in_array(array[array[1,2]], ARRAY[ARRAY[1,2], ARRAY[3,4],
>> ARRAY[5,6]]);
>>
>> -- or --
>>
>> create or replace function slice_in_array(needle anyarray, haystack
>> anyarray) returns bool as
>> $$
>>   select array[$1] in (select $2[v:v] from generate_series(1,
>> array_upper($2, 1)) v);
>> $$ language sql immutable;
>>
>> select slice_in_array(ARRAY[1,2], ARRAY[ARRAY[1,2], ARRAY[3,4],
>> ARRAY[5,6]]);
>>
>> (not really sure what the deal is with the operator)

You're welcome.  For posterity, the reason why the @> operator didn't
work for you is that it only looks at the 'element' level, not the
slice level (since 1 and 6 were each in the complete array, it
matched).  Not sure if that's the greatest behavior, but it's not
'wrong'.  Basically, you can think if it as unnest(a) @> unnest(b).

merlin