[Zope] OT: Manual unique sequences in RDBMS

[John Morton]

On Tue, 13 Jun 2000 13:58:59 -0400 you wrote:

 > Hello Zopistas,
 > 
 > This is a little off-topic but I had no other place to ask. I'm using
 > Zope & PostgreSQL together on a intranet accounting system (BTW, it's
 > Open Source and I'll post it soon.)
 > 
 > Anyway, I have several tables related by ids (keys), and I have to
 > create these keys by myself. They're of the form YYYYMMDDCCCCCC (Y:
 > year, M: month, D: day, C: a consecutive transaction number that I must
 > generate) and they must be unique. Of course, from day to day, these
 > numbers are guaranteed to be unique, my problem relies on
 > Intra-Day-Uniqueness (TM).
 > 
 > How could I generate the consecutive part? Unfortunately, I can't use
 > the underlaying RDBMS auto-numbering or auto-sequence feature. Since the
 > keys _must_ conform to the above scheme. All I can count on is on the
 > database rows (an ideal solution would be conceptually independent of
 > both Zope and PostgreSQL).
 > 
 > I was even thinking of having a separate table having the last
 > transaction number stored in a single row which would be updated by
 > locking the table... just a thought. Of course, this would be a
 > multiuser environment. Any one?

The auto number stuff in postgresql is basically a sequence (which is
really a one row table), and a function that get's the latest value from
some sequence. What you need to do is create the sequence as usual (might
as well make it cyclic as well), and create a new function that
automatically takes the latest value of that sequence using
nextval('sequence_name') and the current time, and rolls them together as 
the id. Then just use that function to generate the default sequence
value.

John