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 <[EMAIL PROTECTED]> Date: Thu, 20 Jan 2005 00:05:40 +0000 Subject: Re: [GENERAL] sorting library of congress numbers To: Rick Schumeyer <[EMAIL PROTECTED]> On Wed, 19 Jan 2005 18:35:42 -0500, Rick Schumeyer <[EMAIL PROTECTED]> wrote: > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> > > 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 PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
