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