Mark Firestone wrote:
> 
> ----- Original Message ----- 
> From: "Michael Neumann" <mneumann / ntecs.de>
> To: "ruby-talk ML" <ruby-talk / ruby-lang.org>
> Sent: Wednesday, July 21, 2004 3:42 PM
> Subject: Re: ruby postgresql question
> 
>>can you describe the table layout in sql?
> 
> 
> Sure.  Here is the sql statement for a message board table
> 
>  @db.exec("CREATE TABLE #{table} (delete boolean DEFAULT false, \
>            locked boolean DEFAULT false, number int PRIMARY KEY, \
>             m_to varchar(40), \
>            m_from varchar(40), msg_date timestamp, subject varchar(40),\
>            msg_text text, exported boolean DEFAULT false)")
> 
> 
>>what does "reading individual messages in _each direction_" mean? Has
>>this something to do with the hierarchy of the messages? (In-response-To
>>etc.)?
> 
> 
> Ok.  It's an old text (telnet) based Bulletin Board System.  You go to look
> at messages
> post since you last read them.  The system find this point for you.  Then
> you press enter at the prompt to get the new messages, one at a time.  Or
> you can jump to a particular message by typing it's number, or you can go
> backwards, if you like.

aha, now I understand :-)

>>I don't really understand :-) [about making the table...]
> 
> 
> Well, messages get deleted, but the message numbers in the table don't
> change.  The message numbers (when you are reading them) *do* change because
> they are always 1..<highest message> so the system needs to know which
> actual message to pull up when you type it's number in.  They won't be the
> same, after one message is deleted.  So I am doing a ...
> 
> 
>  for row in @db.query("SELECT number FROM #{table} ORDER BY number")
>   hash << row[0].to_i
>  end


Okay, you can use Cursors if you want:

   BEGIN;
     DECLARE CURSOR c SCROLL CURSOR FOR
       SELECT number FROM table ORDER BY number;

     FETCH c;

     FETCH BACKWARD 1 IN c;

     CLOSE c;
   END;

Look for FETCH and DECLARE CURSOR in the Postgres docs.

Regards,

   Michael