From: "Stuart McGraw" <smcg2297@frii.com>
To: <pgsql-sql@postgresql.org>
Subject: data dependent sequences?
Date: Sun, 15 Jul 2007 09:28:26 -0600
Message-ID: <AKEFJAHAPDBEDKIICNJKMEJPCDAA.smcg2297@frii.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="shift_jis"
Content-Transfer-Encoding: 7bit
Importance: Normal

Advice requested :-)  I have a table like:

CREATE TABLE items (
    id INT,
    typ INT    ...
    PRIMAY KEY (seq,typ));

I would like 'id' to be like a SERIAL except that I
want independent sequences for each value of 'typ'.

So if 'items' is:

   id   typ
  ----+-----
    1   'a'
    2   'a'
    3   'a'
    1   'b'

then doing:

  INSERT items(typ) VALUES('a');
  INSERT items(typ) VALUES('b');

will result in:

   id   typ
  ----+-----
    1   'a'
    2   'a'
    3   'a'
    1   'b'
    4   'a'
    2   'b'

Because there can be dozens of values of 'typ'
and new ones added not infrequently, creating
a postgresql sequence for each seems awkward.

Are there other ways I could accomplish this?