Opps... resending to list as well. Perhaps someone can add more insight below.

And check the doentation at
[url]http://borg.postgresql.org/docs/8.0/interactive/extend.html[/url] .


---------- Forwarded message ----------
From: Mike Rylander <mrylander>
Date: Thu, 20 Jan 2005 00:05:40 +0000
Subject: Re: [GENERAL] sorting library of congress numbers
To: Rick Schumeyer <rschumeyerieee.org>


On Wed, 19 Jan 2005 18:35:42 -0500, Rick Schumeyer <rschumeyerieee.org> wrote:
> > -----Original Message-----
> > From: [email]pgsql-general-ownerpostgresql.org[/email] [mailto:pgsql-general-
> > [email]ownerpostgresql.org[/email]] On Behalf Of Mike Rylander
> > Sent: Wednesday, January 19, 2005 6:10 PM
> > To: Rick Schumeyer; PgSql General
> > Subject: Re: [GENERAL] sorting library of congress numbers
> >
> > On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer <rschumeyerieee.org>
> > wrote:
> > >
> > > I have a table where one of the columns is a library of congress number.
> > >
> > > I would like to be able to ORDER BY this column.
> > >
> >
> > First off, by LOC numbers do you mean Title Control Numbers like
> > "o00325992" or "i0824506030" with an optional vendor name in front of
> > them?
>
> I mean the library of congress classification numbers. For example,
> the book "PostgreSQL" by Douglas & Douglas has the classification:
> QA76.9.D3 D685 2003
>
Ah, it wasn't clear that you meant Call Numbers from your original post.

The easiest thing to do would be to create a composite type to hold
each part of the call number. Then you can use the standard
comparison ops for each part and just sort in a normal ORDER BY
clause. I haven't tested any of this, but it may help you get
started.

CREATE DOMAIN txt_loc AS TEXT NOT NULL CHECK ( LENGTH(VALUE) BETWEEN 1 AND 3);
CREATE DOMAIN num_loc AS NUMERIC NOT NULL CHECK (VALUE BETWEEN 0 AND 9999.99);

-- probably want a domain for each part to supply checks...

CREATE TYPE call_number AS (
topic_letters txt_loc, -- QA
topic_number num_loc, -- 76.9
cutter TEXT, -- D3
opt_topic TEXT, -- D685
opt_year INT,
opt_vol INT
);

Then, a function to display them:

CREATE FUNCTION format_cn ( call_number ) RETURNS TEXT AS '
SELECT $1.topic_letters || $1.topic_number || ''.'' || $1.cutter ||
COALESCE('' '' || $1.opt_topic,'''') || COALESCE('' '' ||
$1.opt_year,'''') ||
COALESCE('' V.'' || $1.opt_vol,'''');
' LANGUAGE SQL;

Now, to sort a table like this:

CREATE TABLE title_list (
id SERIAL,
call_num call_number,
title TEXT
);

use:

SELECT title, format_cn(call_num)
FROM title_list
ORDER BY call_num.topic_letters, call_num.topic_number, call_num.cutter,
call_num.opt_topic, call_num.opt_year, call_num.opt_vol;


--
Mike Rylander
[email]mrylander[/email]
GPLS -- PINES Development
Database Developer
[url]http://open-ils.org[/url]


--
Mike Rylander
[email]mrylander[/email]
GPLS -- PINES Development
Database Developer
[url]http://open-ils.org[/url]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])