From: Guy Fraser <guy@incentre.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Gist indexes on int arrays
Date: Tue, 04 Mar 2003 11:40:17 -0700
Message-ID: <3E64F311.30203@incentre.net> (raw)
References: <Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net
Subject: Re: Gist indexes on int arrays
In-Reply-To: <3E64F311.30203@incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox