X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0E01AD1E9EE for ; Thu, 29 Apr 2004 16:34:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 40789-09 for ; Thu, 29 Apr 2004 16:34:14 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 69F60D1B52D for ; Thu, 29 Apr 2004 16:34:12 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3TJYDQQ014670; Thu, 29 Apr 2004 15:34:13 -0400 (EDT) To: "Antal Attila" Cc: pgsql-sql@postgresql.org Subject: Re: Multi ordered select and indexing In-reply-to: <000001c42937$637db7a0$0b02010a@atesz> References: <000001c42937$637db7a0$0b02010a@atesz> Comments: In-reply-to "Antal Attila" message dated "Fri, 23 Apr 2004 15:32:13 +0200" Date: Thu, 29 Apr 2004 15:34:13 -0400 Message-ID: <14669.1083267253@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200404/322 X-Sequence-Number: 17697 "Antal Attila" writes: > CREATE OPERATOR CLASS int4_reverse_order_ops > FOR TYPE int4 USING btree AS > OPERATOR 1 /< , > OPERATOR 2 /<= , > OPERATOR 3 /= , > OPERATOR 4 />= , > OPERATOR 5 /> , > FUNCTION 1 int4_reverse_order_cmp(int4, int4); This is the wrong way to go about it. A useful descending-order opclass simply rearranges the logical relationships of the standard comparison operators. You do need a new comparison function, but nothing else: CREATE OPERATOR CLASS int4_reverse_order_ops FOR TYPE int4 USING btree AS OPERATOR 1 > , OPERATOR 2 >= , OPERATOR 3 = , OPERATOR 4 <= , OPERATOR 5 < , FUNCTION 1 int4_reverse_order_cmp(int4, int4); Now you can just use ASC/DESC in your ORDER BY ... regards, tom lane