Message-ID: <3E64F311.30203@incentre.net>
Date: Tue, 04 Mar 2003 11:40:17 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Gist indexes on int arrays
References: <Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.