Re: [HACKERS] If there a bug in the psql or just a feature .

[Hannu Krosing]

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