Re: Gist indexes on int arrays

I can help with your first() and last() functions.

...snip...
>
>
> Absolutely.
> Moreover if your array element positions that you want to compare
> against(e.g attr_a[1], or attr_b[n], where n is the last element) are
> known, then you could have a function "first" that returns
> the first element (you must pay attention to nulls and out of bound
> situations), and a function "last" that returns the last element.
> Then you could have normal btree indexes on first(attr_a), and on
> last(attr_b), but unfortunately not an index on both.
>
>
>
...snip...

Here is some code I wrote that works in 7.2 and 7.3 that helps.

This function current is designed for a single dimentional text array, but can
be converted to work with integers very easily, I just dodn't have a proof
right now.

--
-- Start of function
--
CREATE FUNCTION array_size (TEXT[]) RETURNS INT AS '
DECLARE
array ALIAS FOR $1;
dim INT;
BEGIN
SELECT INTO dim
rtrim(ltrim(ltrim(array_dims(array),''[012345679''),'':''),'']'')::INT ;
IF dim IS NULL
THEN
dim := 0 ;
END IF;
RETURN dim;
END;' LANGUAGE plpgsql;
--
-- End function
--
--Start of Proof
--
CREATE TABLE cruft(array TEXT[]);
INSERT INTO cruft VALUES('{data1,data2,data3}');
SELECT array,array_size(array) FROM cruft;
--
-- array | array_size
-----------------------+------------
-- {data1,data2,data3} | 3
--(1 row)
--
-- End Proof
--

To get the first and last values :

SELECT array[1] as first,array[array_size(array)] as last FROM cruft;
--
-- first | last
---------+-------
-- data1 | data3
--(1 row)
--

I hope this helps.