Default Function/Operator for "order by"? - Mailing list pgsql-general

From John Callahan
Subject Default Function/Operator for "order by"?
Date
Msg-id Pine.SOL.3.91.981027183130.25284y-100000@gemini
Whole thread Raw
List pgsql-general
Hi -

Many of our applications use numerically based data that sometimes has
some non-numeric component: room numbers, version numbers, key
identifiers (the kind of keys that open doors), etc.  This necessarily
requires us to use character based columns to store this information...
however, this means on a sort, room number "27A" shows up between room
number "279" and room number "280", which confuses people.

I've written a set of functions called "varcharnum{cmp,lt,le,gt,ge}" that
behave identically to the internal "varcharcmp,varcharlt,..." except
any series of digits is evaluated as an integer... a numeric sort.

This works great, except it will take a while to redo all my select
statements to "order by room using <#". "<#" is my "less than numerically"

I'm wondering what the relationship between a type (say varchar) and the
operator or function used to sort it is, and if I could just create another
type "varcharnum" that is identical to "varchar" except it uses my
functions for sorting?

Thanks in advance.
John
--
John Callahan <jcallaha@willamette.edu>  |Assistant Director, Network Services
Willamette Integrated Technology Services|Willamette University, Salem, OR
Phone: (503) 375-5495 Fax: (503) 375-5456|http://www.willamette.edu/~jcallaha




pgsql-general by date:

Previous
From: The Hermit Hacker
Date:
Subject: Re: [GENERAL] 8k limit
Next
From: "SeeHyun Lee"
Date:
Subject: Vacuum problem.