Re: Workaround or user defined type - Mailing list pgsql-novice

What you probably want is to use a PostgreSQL sequence
to create the serial part of the ticket number.  I
personally would store your "ticket number" as a
separate integer and date, with the integer being the
primary key of the record.  Not only will this make
searching for these records easier, but you can use
PostgreSQL's SERIAL type to do the grunt work of
creating the necessary sequences.

If you really want the trouble ticket numbers to look
like DDMMYYYY-SERIAL then simply do something like:

processdata=> SELECT to_char(now()::date, 'DDMMYYYY')
|| '-' || lpad(id::varchar, 10, '0') AS ticket_no FROM
foo;
     ticket_no
---------------------
 06112001-0000000001
 06112001-0000000002
 06112001-0000000003
(3 rows)

That gives you exactly what you need without having to
code any of it yourself.  PostgreSQL will happily make
sure that your sequence derived primary keys are
always unique and you can easily serve up ticket
numbers that look like what your clients expect.  It
will even be *fast*.  Chances are good that sequences
will be must faster than anything else you are likely
to cook up.

The downside is that failed transactions will leave
holes in your sequence, (it might go 1, 2, 5) but I
can't imagine that being a major problem with your
application.

By the way, you will notice that I used lpad to give
the sequence a fixed width.  It probably would be
better if you didn't do this.  On the other hand
9,999,999,999 is an awful lot of trouble tickets.

Jason

--- Juan Jose Natera Abreu <jnatera@net-uno.net>
wrote:
> Hello people,
>
> 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?
>
> The other option I see, is creating a user defined
> type that will take
> care of keeping the sequence for me. I read the
> apropiate page at the
> programmers manual, but i think i would need more
> information. Any
> resources?
>
> Regards,
>
> Juan Jose
> --
> fortune generated signature:
> Brook's Law: Adding manpower to a late software
> project makes it later
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com