Re: Workaround or user defined type

Juan Jose Natera Abreu <jnatera@net-uno.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