Quick Links

Custom column ordering

From:	Steven Xu <stevenx(at)yorku(dot)ca>
To:	pgsql-general(at)postgresql(dot)org
Subject:	Custom column ordering
Date:	2016-03-01 20:54:15
Message-ID:	OF4914D9AC.730EB9E5-ON85257F69.0072D4B1-85257F69.0072D4B3@yorku.ca
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

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 <div></div>

Attachment	Content-Type	Size
unknown_filename	text/html	7.3 KB

Responses

Re: Custom column ordering at 2016-03-05 14:23:09 from Emre Hasegeli

Browse pgsql-general by date

	From	Date	Subject
Next Message	Artur Zakirov	2016-03-01 21:04:36	Re: commit time in logical decoding
Previous Message	Igor Neyman	2016-03-01 19:02:33	Re: multiple UNIQUE indices for FK