Create one sequence per owner and write a function/trigger that will check
the owner id for the record and call the appropriate nextval(sequence) to
get the next value for that owner and replace the id with the generated
value.
If new owner IDs are going to be generated, however, this will not be
maintainable.
I would seriously question whether what you are asking is truly necessary.
Using the owner ID a timestamp field you can retrieve the records from this
table in the same order they were added. You can then calculate the "row
number" in the resultant query to get the sequencing desired. Thus you can
use the sequence as it was designed to generate a unique integer value for
the table whenever necessary (unique, but not necessarily
sequential/without-gaps).
You are trying to give contextual meaning to the values generated by
serial/sequence but that is not what they are designed for. If you really
need to create the "index id" at the time of record creation you should
write you own ID generator routine - making sure you deal with concurrency
issues.
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zhi-Qiang Lei
Sent: Friday, February 25, 2011 8:10 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] The scope of sequence
Dear All,
I've got a composite key in a table. The keys are ID and owner ID. The type
of the ID should be a serial, additionally I hope the ID to be increasable
per owner ID. ( The ID will be increased with the different owner ID) Does
anyone know how to implement it? Thanks in advance.
Best regards,
Zhi-Qiang Lei
zhiqiang.lei@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
