Subject: Rewrite rule question (or is it something else)?

From: [email-masked] (Paul M. Aoki)
Date: Mon, 18 Jul 94 19:31:09 -0700

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

------- Forwarded Message

 Newsgroups:  comp.databases
 From:  Paul M. Aoki <[email-masked]>
 Subject:  Re: Rewrite rule? (postgres)
 Date:  19 Jul 1994 01:14:27 GMT

 [email-masked] (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
 	[email-masked]
 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
  [email-masked]  |  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 "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================