Hi all,
I'm trying to order some rows based on port names, a text column, using some domain-specific knowledge for Netdisco, an open-source application.
In particular, I'm trying to do this without having to redo the entire design for the database. Note that in this database, there are no foreign key constraints, mostly because they weren't considered in the original inception of the software.
The rough idea of the solution I've come up with is to create a new composite data type called "port" with a single text column. Then I created the relevant comparison functions, operators and operator classes under the "port_ops" operator family. Then, I created a function "cast_to_port(text)" that casts "text" data types to "port" (which simply creates a tuple with the single text value, see the code for details). Finally, I created an index on the "device_port" table with "cast_to_port(port)" as the indexed column using "port_ops". However, when I run "select port from device_port order by cast_to_port(port)", it doesn't use the index I created and doesn't even order using the operators I created. Instead, it orders by the lexicographical ordering of the original text column.
Questions:
- Why is PostgreSQL not using the functional index I created and why is it not being ordered correctly?
- Is creating a separate data type and using a functional index on the casts to this data type the right approach to a custom ordering?
Steven
Details:
Creating the "port" type:
create type port as (f1 text);
Creating the comparator function, substituted with much shorter code as an example. I can include the original code, but it's much shorter to provide this.
It returns -1 if the first argument is "less than" the second argument, 0 if they are equal and 1 otherwise. In this example, "less than" means lexicographically greater, because of the negation.
create or replace function port_cmp(port,port)
RETURNS integer as
$$
my ($a, $b) = @_;
return -( $a cmp $b ); // comparison function does the opposite of cmp
$$ language plperl;
Creating the "port_ops" operators and operator classes for the "port" type:
CREATE OR REPLACE FUNCTION port_lt(port, port)
RETURNS boolean AS
$$
BEGIN
RETURN port_cmp($1, $2) < 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION port_gt(port, port)
RETURNS boolean AS
$$
BEGIN
RETURN port_cmp($1, $2) > 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION port_lte(port, port)
RETURNS boolean AS
$$
BEGIN
RETURN port_cmp($1, $2) <= 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION port_gte(port, port)
RETURNS boolean AS
$$
BEGIN
RETURN port_cmp($1, $2) >= 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION port_eq(port, port)
RETURNS boolean AS
$$
BEGIN
RETURN port_cmp($1, $2) = 0;
END;
$$ LANGUAGE plpgsql;
CREATE OPERATOR < ( PROCEDURE=port_lt, LEFTARG=port, RIGHTARG=port);
CREATE OPERATOR <= ( PROCEDURE=port_lte, LEFTARG=port, RIGHTARG=port);
CREATE OPERATOR >= ( PROCEDURE=port_gte, LEFTARG=port, RIGHTARG=port);
CREATE OPERATOR > ( PROCEDURE=port_gte, LEFTARG=port, RIGHTARG=port);
CREATE OPERATOR = ( PROCEDURE=port_eq, LEFTARG=port, RIGHTARG=port);
CREATE OPERATOR CLASS port_ops DEFAULT
FOR TYPE port USING btree
AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 port_cmp(port, port)
;
Creating the function to cast "text" data to "port" data:
CREATE OR REPLACE FUNCTION cast_to_port(text)
RETURNS port
LANGUAGE sql
AS $function$
SELECT ($1);
$function$ IMMUTABLE;
Creating the index:
CREATE INDEX idx_device_port_port_proper on device_port (cast_to_port(port) port_ops desc);
Sample table structure, shortened for brevity:
Table "public.device_port"
Column | Type | Modifiers
--------------+-----------------------------+------------------------
ip | inet | not null
port | text | not null
