sorting library of congress numbers

On Wed, 2005-01-19 at 16:37, Rick Schumeyer 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.

Currently, the column is a varchar. Unfortunately, a normal string
comparison

will not work.

First, has anyone done this already?

If not, I’m thinking I would need to:

1) define a new type to hold LOC numbers.

2) write a function that determines less than, greater than

It seems like I’m missing something here. I have not yet defined a
new

type…is defining less/greater part of that process?

Is there a simpler way to do this?

You might want to do something along the lines of storing the PARTs of
the loc number, then using lpad/rpad in a function to put them together
in such a way to as to render them orderable. Is there an URL on how
LOC numbers are ordered?

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?

Currently, the column is a varchar. Unfortunately, a normal string
comparison will not work.

First, has anyone done this already?

If you mean ISBN or ISSN numbers then there is a contrib module that
may help, though I don't think it implements a new type, just
functions to validate and generate them.

If not, I'm thinking I would need to:

1) define a new type to hold LOC numbers.

2) write a function that determines less than, greater than

It seems like I'm missing something here. I have not yet defined a new type…is defining less/greater part of that process?
Is there a simpler way to do this?

You could probably just write some comparison functions, create a new
operator class, and assign them names like "~>" and "~<". See the
documentation here:
http://www.postgresql.org/docs/8.0/interactive/xoper.html .

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Wednesday, January 19, 2005 5:54 PM
To: Rick Schumeyer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] sorting library of congress numbers

On Wed, 2005-01-19 at 16:37, Rick Schumeyer 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.

Currently, the column is a varchar. Unfortunately, a normal string
comparison

will not work.

First, has anyone done this already?

If not, I'm thinking I would need to:

1) define a new type to hold LOC numbers.

2) write a function that determines less than, greater than

It seems like I'm missing something here. I have not yet defined a
new

type.is defining less/greater part of that process?

Is there a simpler way to do this?

You might want to do something along the lines of storing the PARTs of
the loc number, then using lpad/rpad in a function to put them together
in such a way to as to render them orderable. Is there an URL on how
LOC numbers are ordered?

LOC numbers are described at:

http://library.dts.edu/Pages/RM/Helps/lc_call.shtml

I thought about breaking the pieces up. I will have to think a bit more
about whether that will work.

-----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

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not write a function that converts the LOC number to an integer,
such that sorting on those integers will sort the numbers correctly?
Apparently you can even build an index on this type of function now, to
accelerate the sort.

cvt_loc(TEXT) -> INTEGER

ORDER BY cvt_loc(loc_num)

or something like that...

On Jan 19, 2005, at 6:35 PM, Rick Schumeyer 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

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB7vdJ7aqtWrR9cZoRAvd9AJwO9Ev7ZaU3IEXtssjmV6pyj5VcbwCcCtcL
oNbZSA44H4GVfGXMGC5tFZM=
=coyv
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com