Re: Workaround or user defined type

Juan Jose Natera Abreu <jnatera(at)net-uno(dot)net> writes:
> I am working on a trouble ticket system in Perl, usually those systems
> identify a ticket by a string composed of DDMMYYYY-SERIAL, (DD = day,
> MM= month, YYYY= year, SERIAL= a serial number for the date string).

> I am not sure how to generate these identifiers, in case I generate
> them manually i must provide a mechanism to insure the sequence, like
> locking the table for reading/writing, get the last value, insert the
> new one and then release the lock. However i think this could be a big
> performance killer. Any ideas?

How about

regression=# create sequence serial_seq;
CREATE
regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
?column?
--------------
2001-11-06-1
(1 row)

regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
?column?
--------------
2001-11-06-2
(1 row)

If you don't like this particular formatting then you could use to_char
to format the date as you wish.

Note that with this solution the serial numbers increase forever; they
don't reset to 1 at midnight. I consider that a preferable behavior
anyway, but if you want the other, you could have a cron job reset the
sequence object once a day (see setval()).

regards, tom lane