Thread: sorting library of congress numbers
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?
Any advice is appreciated.
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