Re: array support patch phase 1 patch

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>Next question: should I roll the three array related patches floating
>>around (phase2, phase3, polycoerce) into one big patch again? It's
>>difficult for me to continue to make progress without doing that.
>
> If it's easier at your end.

Attached is the "super combo" array patch ;-)

It includes the following previously sent patches:
array-phase2.19.patch
array-phase3.05.patch
array-polycoerce.3.patch

It also includes the following additional changes:
1) aclitem type now has an equality operator
2) examine_attribute() now checks for the element type's equality and
ordering operators when dealing with a varlena array type
3) There are now "<>, ">", "<", "<=", and ">=" operators for
array-to-array comparison, using semantics similar to text's
character-to-character comparison.
4) There is also now a btarraycmp() function and other required
btree support. BTree indexes can now be created on varlena arrays.

Below are some examples of the new (since earlier patches)
functionality. CVS tip from this morning plus this patch compiles
cleanly and passes all 90 regression tests on my dev box. If there are
no objections, please apply.

Thanks,

Joe

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 distinct array(select f1 from tse);
?column?
-------------
{1,2,3,4,5}
(1 row)

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

regression=# select * from tse where f2 < array[1,2,3];
f1 | f2 | f3
----+---------+---------
4 | {1,1,1} | {a,a,a}
(1 row)

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

regression=# select * from tse where f2 > array[1,2,3];
f1 | f2 | f3
----+------------+-------------
1 | {69,42,54} | {g,d,e}
3 | {2,99,0} | {Tom,Bruce}
5 | {5,6,7} | {a,b,c}
(3 rows)

regression=# select * from tse where f2 >= array[1,2,3];
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 f2 != array[1,2,3];
f1 | f2 | f3
----+------------+-------------
1 | {69,42,54} | {g,d,e}
3 | {2,99,0} | {Tom,Bruce}
4 | {1,1,1} | {a,a,a}
5 | {5,6,7} | {a,b,c}
(4 rows)

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

regression=# select * from tse where f3 >= array['Tom'] and f3 < array['a'];
f1 | f2 | f3
----+----------+-------------
3 | {2,99,0} | {Tom,Bruce}
(1 row)

regression=# create index arridx1 on tse(f2);
CREATE INDEX
regression=# set enable_seqscan to off;
SET
regression=# explain analyze select * from tse where f2 = array[1,2,3];
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using arridx1 on tse (cost=0.00..4.68 rows=2 width=68)
(actual time=0.07..0.07 rows=1 loops=1)
Index Cond: (f2 = '{1,2,3}'::integer[])
Total runtime: 0.15 msec
(3 rows)