Re: interesting sequence - Mailing list pgsql-sql

My previous reply was intended for John.

On Tue, Jul 5, 2011 at 1:11 PM, Kevin Crain <kevin.crain1@gmail.com> wrote:
> You don't need a loop there.  Assuming your order id field is of type
> varchar you can just build the first part of your string and then do a
> count to get the last part using a LIKE comparison:
>
> select count(id_order) + 1  from sometable WHERE id_order LIKE 'O-20110704 -%';
>
> If you do this inside a function it will be like running it in a
> transaction so you shouldn't have to worry about it being a multi-user
> system.
>
>
>
> On Tue, Jul 5, 2011 at 12:38 PM, Samuel Gendler
> <sgendler@ideasculptor.com> wrote:
>>
>>
>> On Tue, Jul 5, 2011 at 6:52 AM, John Fabiani <johnf@jfcomputer.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
>>
>>
>