Обсуждение: interesting sequence
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)LOOPcount = 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. Johnf
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
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
>
>
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
>>
>>
>
On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain 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. > > > I like this - looks better than what I'm currently doing. Thanks Johnf
On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani <johnf@jfcomputer.com> wrote:
On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote:I like this - looks better than what I'm currently doing. Thanks
> 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.
>
>
>Johnf
It is simpler, but it will result in id collision if two inserts runs at the same time, particularly if the count query takes a while to run, so be prepared to handle that. Make sure you have an index which can satisfy that count query quickly. If you are not using the C locale for your database, that means you must create an index on that column that uses text_pattern_ops or varchar_pattern_ops (depending on if it is text or varchar column) so that postgresql can use the index for that comparison, otherwise LIKE clauses will force a sequential scan of the whole table every time. C locale does byte by byte text comparison, so the special index isn't required.
That's why you need to do this inside a function. Basically just make an insert function for the table and have it calculate the count and do the insert in one transaction. On Tue, Jul 5, 2011 at 5:41 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote: > > > On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani <johnf@jfcomputer.com> wrote: >> >> On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain 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. >> > >> > >> > >> >> I like this - looks better than what I'm currently doing. Thanks >> Johnf >> > > It is simpler, but it will result in id collision if two inserts runs at the > same time, particularly if the count query takes a while to run, so be > prepared to handle that. Make sure you have an index which can satisfy that > count query quickly. If you are not using the C locale for your database, > that means you must create an index on that column that uses > text_pattern_ops or varchar_pattern_ops (depending on if it is text or > varchar column) so that postgresql can use the index for that comparison, > otherwise LIKE clauses will force a sequential scan of the whole table every > time. C locale does byte by byte text comparison, so the special index > isn't required. > http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html > >
On 2011-07-06, Kevin Crain <kevin.crain1@gmail.com> wrote: > That's why you need to do this inside a function. Basically just make > an insert function for the table and have it calculate the count and > do the insert in one transaction. you will still get duplicates, so include code in the function to retry if there is an error. -- ⚂⚃ 100% natural
On 06/07/11 01:52, John Fabiani wrote: <blockquote cite="mid:201107050652.22187.johnf@jfcomputer.com" type="cite"><pre wrap="">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)LOOPcount = 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. Johnf </pre></blockquote> Hi John,<br /><br /> How about using a table to hold the latest sequence for each order type and date,along with a function to insert a new order?<br /><br /> (I've included the code to test the idea and the results, Iam using 9.1beta2, but it should not make any difference - I think!):<br /><br /><br /><font face="Courier New, Courier,monospace">DROP TABLE IF EXISTS my_order;<br /> DROP TABLE IF EXISTS order_sequence;<br /><br /><br /> CREATE TABLEmy_order<br /> (<br /> order_num text PRIMARY KEY,<br /> payload text<br /> );<br /><br /><br /> CREATETABLE order_sequence<br /> (<br /> type int,<br /> day date,<br /> seq int NOT NULL,<br/> PRIMARY KEY (type, day)<br /> );<br /><br /><br /> CREATE OR REPLACE FUNCTION create_my_order<br /> (<br /> IN type int,<br /> IN day date,<br /> IN payload text<br /> ) RETURNS VOID<br /> AS<br /> $$<br /> DECLARE<br /> v_order_num text;<br /> v_seq_old int;<br /> v_seq_new int;<br /> BEGIN<br/> SELECT <br /> os.seq<br /> FROM <br /> order_sequence os <br /> WHERE<br /> os.type = create_my_order.type AND<br /> os.day = create_my_order.day<br /> INTO<br /> v_seq_old;<br /> <br /> IF v_seq_old IS NULL THEN<br /> v_seq_new:= 1;<br /> INSERT INTO order_sequence(type, day, seq)<br /> VALUES (type, day, v_seq_new); <br /> ELSE<br /> v_seq_new := v_seq_old + 1;<br /> UPDATE <br /> order_sequence AS os<br /> SET<br /> seq = v_seq_new<br /> WHERE<br/> os.type = create_my_order.type AND<br /> os.day = create_my_order.day;<br /> END IF;<br /> <br /> v_order_num := type::text ||<br /> '-' ||<br /> to_char(day, 'YYMMDD') || <br /> '-' ||<br /> v_seq_new::text;<br/> <br /> INSERT INTO my_order(order_num, payload)<br /> VALUES (v_order_num,payload);<br /> END;<br /> $$ LANGUAGE plpgsql<br /> VOLATILE<br /> ;<br /><br /><br /> SELECT create_my_order(0, '2010-03-24', 'order #1 details');<br /> SELECT create_my_order (0, '2010-03-24', 'order #2 details');<br/> SELECT create_my_order (0, '2010-06-15', 'order #3 details');<br /> SELECT create_my_order (5, '2010-03-24','order #4 details');<br /> SELECT create_my_order (0, '2010-06-15', 'order #5 details');<br /> SELECT create_my_order(3, '2010-06-14', 'order #6 details');<br /><br /> TABLE order_sequence;<br /> TABLE my_order;<br /><br /><br/> ////////// This outputs the following:<br /><br /> type | day | seq <br /> ------+------------+-----<br /> 0 | 2010-03-24 | 2<br /> 5 | 2010-03-24 | 1<br /> 0 | 2010-06-15 | 2<br /> 3 | 2010-06-14 | 1<br/> (4 rows)<br /><br /> order_num | payload <br /> ------------+------------------<br /> 0-100324-1 | order#1 details<br /> 0-100324-2 | order #2 details<br /> 0-100615-1 | order #3 details<br /> 5-100324-1 | order #4 details<br/> 0-100615-2 | order #5 details<br /> 3-100614-1 | order #6 details<br /> (6 rows)</font><br /><br />
On 06/07/11 21:47, Gavin Flower wrote:<br /><br /> I forgot the format required of the order number, so to get the fullyesr, I should have used:<br /><font face="Courier New, Courier, monospace">to_char(day, 'YYYYMMDD')</font><br /><br/> [...] <blockquote cite="mid:4E142F1A.8060900@archidevsys.co.nz" type="cite"><font face="Courier New, Courier, monospace"> v_order_num := type::text ||<br /> '-' ||<br /> to_char(day,'YYMMDD') || <br /> '-' ||<br /> v_seq_new::text;</font></blockquote>[...]<br /><br /> Cheers,<br /> Gavin
IF this field is unique you shouldn't get duplicates from a function; the transaction will either succeed or fail; the beauty of a function is that you can return an error message. I personally prefer to handle errors at the application level, but if you have admins running ad-hoc queries on the database level doing inserts then you definitely do need to handle that properly. Triggers are handy in that regard. You can do loops to check for collisions...I haven't done that so don't know the best way to code that though. On Wed, Jul 6, 2011 at 5:28 AM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2011-07-06, Kevin Crain <kevin.crain1@gmail.com> wrote: >> That's why you need to do this inside a function. Basically just make >> an insert function for the table and have it calculate the count and >> do the insert in one transaction. > > you will still get duplicates, so include code in the function to > retry if there is an error. > > -- > ⚂⚃ 100% natural > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >