Fwd: sorting library of congress numbers - Mailing list pgsql-general

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

And check the documentation at
http://borg.postgresql.org/docs/8.0/interactive/extend.html .


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


On Wed, 19 Jan 2005 18:35:42 -0500, Rick Schumeyer <rschumeyer@ieee.org> wrote:
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> > owner@postgresql.org] 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 <rschumeyer@ieee.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
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org