Re: Multi row sequence?

On Fri, Dec 17, 2004 at 11:10:12AM -0000, Filip Wuytack wrote:

> Is it possible to have a sequence (as a multirow prim key), where sequence
> (id) only increase per group of data (grp).
>
> E.g.
> +--------+----+---------+
> | grp | id | name |
> +--------+----+---------+
> | fish | 1 | lax |
> | mammal | 1 | dog |
> | mammal | 2 | cat |
> | mammal | 3 | whale |
> | bird | 1 | penguin |
> | bird | 2 | ostrich |
> +--------+----+---------+

PostgreSQL's sequences are simply number generators that return a
unique value. If you want to generate keys in the manner you
describe, then you could use a trigger to calculate what the next
id should be. You'd probably have to lock the table to ensure that
the operation works when multiple transactions are updating at the
same time.

Take a look at the "Triggers" chapter in the documentation, as well
as the "Trigger Procedures" section of the "PL/pgSQL - SQL Procedural
Language" chapter and the "Concurrency Control" chapter.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/