On Tue, 2002-09-10 at 17:22, Vanmunin Chea wrote:
> Hey Tom,
>
> Thanks for the tips, Tom. I have that feeling from the start
> (with the two different implementation) but never actually have a chance
> to confirm with someone.
>
> 1. It there a way to store the dynamic array at all ?
>
>
> I notice psql has a similar type - Single Dynamic Dimensional
> Array. However there isn't any built in operators(<,<=,==,>,>=) for Array
> to do sorting.
>
> 2. Can I write one up ?
See attachment.
Unfortunately I ran out of time before figuring out how to make btree
index use it ;(
Also, in 7.3 there are a lot more ops for in contrib/intarray than was
in 7.2.
-------------
Hannu
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
-- first the 'worker' function
DROP FUNCTION intarray_compare(INT[],INT[]);
CREATE FUNCTION intarray_compare(INT[],INT[]) RETURNS INT AS '
DECLARE
I INTEGER := 1;
BEGIN
LOOP
IF ($1[I] > $2[I]) THEN
RETURN 1;
ELSE
IF ($1[I] < $2[I]) THEN
RETURN -1;
END IF;
END IF;
IF ($1[I] IS NULL) THEN
IF ($2[I] IS NULL) THEN RETURN 0; END IF;
RETURN -1;
END IF;
IF ($2[I] IS NULL) THEN RETURN 1; END IF;
I := I + 1;
EXIT WHEN I > 1000;
END LOOP;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_gt(INT[],INT[]);
create FUNCTION intarray_gt(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) = 1;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_gte(INT[],INT[]);
create FUNCTION intarray_gte(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) >= 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_lt(INT[],INT[]);
create FUNCTION intarray_lt(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) = -1;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_lte(INT[],INT[]);
create FUNCTION intarray_lte(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) <= 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_eq(INT[],INT[]);
create FUNCTION intarray_eq(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) = 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION intarray_neq(INT[],INT[]);
create FUNCTION intarray_neq(INT[],INT[]) RETURNS BOOLEAN AS '
BEGIN
RETURN intarray_compare($1,$2) != 0;
END;
' LANGUAGE 'plpgsql';
-- now greate the operators
CREATE OPERATOR < (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_lt,
commutator = > ,
negator = >=
);
CREATE OPERATOR <= (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_lte,
commutator = >= ,
negator = >
);
CREATE OPERATOR > (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_gt,
commutator = < ,
negator = <=
);
CREATE OPERATOR >= (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_gte,
commutator = <= ,
negator = <
);
CREATE OPERATOR = (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_eq,
commutator = = ,
negator = !=
);
CREATE OPERATOR != (
leftarg = INT[],
rightarg = INT[],
procedure = intarray_neq,
commutator = != ,
negator = =
);
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
