Re: interesting sequence - Mailing list pgsql-sql

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
>
>