From: | Kevin Crain <kevin(dot)crain1(at)gmail(dot)com> |
---|---|
To: | sgendler(at)ideasculptor(dot)com, johnf(at)jfcomputer(dot)com |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: interesting sequence |
Date: | 2011-07-05 20:13:32 |
Message-ID: | CADwm0aj7Ho8KWn0dc5kjVcN5kZn-F2P3Ehs83qa0aPAM9xQbcQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
My previous reply was intended for John.
On Tue, Jul 5, 2011 at 1:11 PM, Kevin Crain <kevin(dot)crain1(at)gmail(dot)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(at)ideasculptor(dot)com> wrote:
>>
>>
>> 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
>>
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Viktor Bojović | 2011-07-05 20:29:03 | overload |
Previous Message | Kevin Crain | 2011-07-05 20:11:11 | Re: interesting sequence |