Thread: Enhancement suggestion

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Allow ASC/DESC direction modifiers for index columns.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Almost every database has this, and it is a pest to have to write a function for every index column
youwant descending.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Now, it is not technically difficult (nothing more than –compare(x,y) instead of compare(x,y) to
producethe ordering) but it would be much better to simply add it to the language.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">It would obviously require at least two changes, one to the grammar and one to the indexing
code.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font></div>

"Dann Corbit" <DCorbit@connx.com> writes:
> Allow ASC/DESC direction modifiers for index columns.

We aren't going to do that, because it would be a meaningless concept
for indexes that don't impose a linear sort order (which is to say,
everything except btrees).  The concept that actually fits into PG's
index structure is to offer reverse-sort-order btree operator classes.
Providing these as standard equipment for all the built-in datatypes
has been discussed several times --- I'm not sure if it's mentioned in
the TODO file but probably it should be.  In the meantime you can cons
up your own reverse order opclass with little effort beyond writing
the one comparison function wrapper.  See the archives for details.
        regards, tom lane

Obviously, you cannot create ordering in hash indexes, which is why
nobody else does that either.

The list of relational database systems that offer asc/desc on btree
index files is quite extensive.

The list of relational database systems that do not offer it is:
1.  PostgreSQL
2.  ?

It will make porting efforts more difficult and more confusing.  If you
have to write operators for dozens of index files it might scuttle the
entire conversion project.

Allowing custom operators allows exactly the same thing, I admit.  But
doing it a different way from everyone else is not a good idea.

It seems trivial to do it.  Every part of a CREATE INDEX statement is
already non-standard to some extent because ANSI/ISO did not include it
(a defect in my opinion).  And every CREATE INDEX statement can look
different than the others because the different index types have
different purposes.

I won't harp on it any more and if the core team is not interested that
is not a problem with me.  I can always do it myself if it annoys me
enough.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, February 02, 2005 9:37 AM
To: Dann Corbit
Cc: pgsql-hackers
Subject: Re: [HACKERS] Enhancement suggestion

"Dann Corbit" <DCorbit@connx.com> writes:
> Allow ASC/DESC direction modifiers for index columns.

We aren't going to do that, because it would be a meaningless concept
for indexes that don't impose a linear sort order (which is to say,
everything except btrees).  The concept that actually fits into PG's
index structure is to offer reverse-sort-order btree operator classes.
Providing these as standard equipment for all the built-in datatypes
has been discussed several times --- I'm not sure if it's mentioned in
the TODO file but probably it should be.  In the meantime you can cons
up your own reverse order opclass with little effort beyond writing
the one comparison function wrapper.  See the archives for details.
        regards, tom lane

On Wed, Feb 02, 2005 at 09:50:16AM -0800, Dann Corbit wrote:
> Obviously, you cannot create ordering in hash indexes, which is why
> nobody else does that either.
> 
> The list of relational database systems that offer asc/desc on btree
> index files is quite extensive.

How many in that list allow you to create custom operator classes?

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)