To: pgsql-sql@postgresql.org
From: "Stuart" <smcg2297@frii.com>
Subject: Re: data dependent sequences?
Date: Tue, 17 Jul 2007 07:34:44 -0600
Lines: 35
Message-ID: <f7ighl$1pv$1@sea.gmane.org>
References: <AKEFJAHAPDBEDKIICNJKMEJPCDAA.smcg2297@frii.com>
	<609880.51564.qm@web54306.mail.re2.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

"chester c young" <chestercyoung@yahoo.com> wrote in message news:609880.51564.qm@web54306.mail.re2.yahoo.com...
> > 
> > CREATE TABLE items (
> >     id INT,
> >     typ INT    ...
> >     PRIMAY KEY (seq,typ));
> > 
> 
> >    id   typ
> >   ----+-----
> >     1   'a'
> >     2   'a'
> >     3   'a'
> >     1   'b'
> >     4   'a'
> >     2   'b' 
> 
> you will need to use pre insert trigger since you cannot use column
> references in default expression.
> 
> you could use this same trigger to either:
> - create sequences as needed and apply the right one
> - with locking, lookup for last id of typ
> - with locking, keep another table of typ and nextval

Thanks, that summerizes the options nicely.

I noticed that sequences are tables with a single row
that defines the sequence properties.  I was hoping 
that there was some way of using a sequence with multiple 
rows to maintain multiplre sequences in a sngle table, 
which would make having a large number of sequences a 
little less cluttered, but sounds like I need to implement
that from scratch.