Re: interesting sequence

On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani <johnf(at)jfcomputer(dot)com> wrote:

> Hi,
>
> I have a special need to create a sequence like function.
>
> "O-20110704 -2" which is
> "O" for order (there are other types)
> "20110704" is for July 4, 2011
> '2' the second order of the day for July 4, 2011
>
> I of course can get the type and date. What I don't know is how to get is
> the
> last number. It would seem to be that I would need a loop to determine if
> the
> next number existed.
>
> LOOP
> --Check to see if the string exist in a table
> -- count = count +1
> -- until I don't find the string
> END LOOP;
>
> but then I thought I could do something like
>
> for $1 in (select string from sometable)
> LOOP
> count = count + 1
>
> or something like this
>
> for i in 1..999 LOOP
> -- check for the existence of the string in a table using 'i'
> -- there will never be 999 orders in one day.
> END LOOP
>
>
> So here is the question what would be the best way for a multi-user system?
> If someone has a better thought - it would be helpful.
>
> BTW I did NOT design the number - in fact it seems silly to me.
>

I'd probably do the following. Create a table to hold the current date as a
string appropriate for use in ids. I'd also create a sequence for each of
the id types. I'd set up a cron job (or equivalent) to run at midnight which
updates the date and resets all of the sequences to 1 within a transaction.
You can probably do all of it in a single query.

Then I'd do inserts which generate the id by concatenating the type initial
with the date and a sequence, probably in an insert trigger on the table if
you are ok with server generated ids. Otherwise, you could do insert with a
subquery which generates the id:

insert into order (id, x, y) values ((select 'O-' || d.datestring || '-' ||
nextval('order_id_sequence') from date_table d), 'x_value', 'y_value');

If you are using hibernate or some other ORM, you can surely use an insert
trigger to generate the id and tell the ORM to use a server generated id.

sequence documentation is here:
http://www.postgresql.org/docs/8.1/static/functions-sequence.html