Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199407190231.TAA14653@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199407190231.TAA09787@faerie.CS.Berkeley.EDU>
From: aoki@CS.Berkeley.EDU (Paul M. Aoki)
To: quandt@eec.umr.edu
Cc: postgres@postgres.Berkeley.EDU
Reply-To: aoki@CS.Berkeley.EDU (Paul M. Aoki)
Subject: Rewrite rule question (or is it something else)?
Date: Mon, 18 Jul 94 19:31:09 -0700
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Mon, 18 Jul 94 19:31:18 -0700
Resent-XMts: smtp

i already posted this to comp.databases.  oh well.  here goes:

------- Forwarded Message

 Newsgroups:  comp.databases
 From:  Paul M. Aoki <aoki@faerie.CS.Berkeley.EDU>
 Subject:  Re: Rewrite rule? (postgres)
 Date:  19 Jul 1994 01:14:27 GMT

 quandt@cs.umr.edu (Brian Quandt) writes:
 >My first question is this the only group for postgres?  Or does anyone
 >know of a listserver out there or another news group?
 
 send mail to
 	postgres-request@postgres.Berkeley.EDU
 with subject "ADD".
 
 (any followups should probably go to the mailing list.)
 
 >I'd like postgres to automatically fill in the "id" field by looking
 >up the largest previous one and adding "1" to it.
 [...]
 >Is this the way to do this, or is there a better cleaner/easier way?
 
 i think i would just use a counter table and some application logic..
 but how about something like this.  this is just a variation on the 
 standard "counter table" solution.
 
 faerie:aoki (30)> monitor yikes
 Welcome to the POSTGRES terminal monitor
 
 Go 
 * create foo (name=char16, id=int4)\g
 
 Query sent to backend is "create foo (name=char16, id=int4)"
 CREATE
 Go 
 * create ctr (id=int4)\g
 
 Query sent to backend is "create ctr (id=int4)"
 CREATE
 Go 
 * append ctr (id=0)\g
 
 Query sent to backend is "append ctr (id=0)"
 APPEND 334392
 Go 
 * define rewrite rule increment is
  on append to foo do [
  replace ctr (id = ctr.id + 1) 
  replace foo (id = ctr.id) where foo.name = new.name
 ]\g
 
 Query sent to backend is "define rewrite rule increment is  on append to foo do [  replace ctr (id = ctr.id + 1)   replace foo (id = ctr.id) where foo.name = new.name ]"
 APPEND 334393DEFINE
 Go 
 * append foo (name = "joe blow")\g
 
 Query sent to backend is "append foo (name = "joe blow")"
 APPEND 334396REPLACEREPLACE
 Go 
 * append foo (name = "jane blow")\g
 
 Query sent to backend is "append foo (name = "jane blow")"
 APPEND 334397REPLACEREPLACE
 Go 
 * retrieve (foo.all)\g
 
 Query sent to backend is "retrieve (foo.all)"
 -----------------------------
 | name        | id          |
 -----------------------------
 | joe blow    | 1           |
 -----------------------------
 | jane blow   | 2           |
 -----------------------------
 
 Go 
 *  
  \q
 faerie:aoki (31)> 

------- End of Forwarded Message
--
  Paul M. Aoki          |  University of California at Berkeley
  aoki@CS.Berkeley.EDU  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================