array support phase 3 patch (was Re: array support patch phase 1 patch)

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>Maybe this takes us
>>back to Peter's suggestion:
>> expression IN (array)
>> expression NOT IN (array)
>> expression operator ANY (array)
>> expression operator SOME (array)
>> (expression) operator (array)
>> (expression) operator ALL (array)
>
> There's a lot to be said for that, if we can think of a way to do it.
> I believe this way would force us to integrate the operations into the
> parser, though, rather than just throwing a few polymorphic functions
> at the problem. It's probably a lot more work :-(

The attached implements the middle 2 examples above. The IN and NOT IN
examples would be ambiguous due to the fact that we already accept
"IN (list_of_expressions)" and "NOT IN (list_of_expressions)".

Specifically implemented:
expression1 operator ANY (expression2)
expression1 operator SOME (expression2)
expression1 operator ALL (expression2)

If expression2 is scalar, it simplifies down to the same as doing:
expression1 operator ANY | SOME | ALL
(select expression2)

If expression2 is an array, the array elements are tested as if you did
this:
expression1 operator ANY | SOME | ALL
(select element_a
union all
select element_b...
union all
select element_n)

Here are a few examples:

create table tse(f1 int, f2 int[], f3 text[]);
insert into tse values(1,array[69,42,54], array['g','d','e']);
insert into tse values(2,array[1,2,3], array['x','y','z']);
insert into tse values(3,array[2,99,0], array['Tom','Bruce']);
insert into tse values(4,array[1,1,1], array['a','a','a']);
insert into tse values(5,array[5,6,7], array['a','b','c']);

regression=# select * from tse where 1 = any (f2);
f1 | f2 | f3
----+---------+---------
2 | {1,2,3} | {x,y,z}
4 | {1,1,1} | {a,a,a}
(2 rows)

regression=# select * from tse where 1 = all (f2);
f1 | f2 | f3
----+---------+---------
4 | {1,1,1} | {a,a,a}
(1 row)

regression=# select * from tse where 'a' != any (f3);
f1 | f2 | f3
----+------------+-------------
1 | {69,42,54} | {g,d,e}
2 | {1,2,3} | {x,y,z}
3 | {2,99,0} | {Tom,Bruce}
5 | {5,6,7} | {a,b,c}
(4 rows)

regression=# select * from tse where 'a' != all (f3);
f1 | f2 | f3
----+------------+-------------
1 | {69,42,54} | {g,d,e}
2 | {1,2,3} | {x,y,z}
3 | {2,99,0} | {Tom,Bruce}
(3 rows)

regression=# select * from tse where 'y' < some (f3);
f1 | f2 | f3
----+---------+---------
2 | {1,2,3} | {x,y,z}
(1 row)

The patch passes all regression tests. It is independent of the phase2
patch submitted earlier this week.

I am already planning to update the array related docs one more time
after the feature freeze, so I'd like to wait until then to document
this. If there are no objections, please apply.

Thanks,

Joe