pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinteresting sequence
10+ messages / 5 participants
[nested] [flat]
* interesting sequence
@ 2011-07-05 13:52 John Fabiani <johnf@jfcomputer.com>
0 siblings, 2 replies; 10+ messages in thread
From: John Fabiani @ 2011-07-05 13:52 UTC (permalink / raw)
To: pgsql-sql
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.
Johnf
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-05 19:38 Samuel Gendler <sgendler@ideasculptor.com>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2011-07-05 19:38 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-05 20:11 Kevin Crain <kevin.crain1@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 2 replies; 10+ messages in thread
From: Kevin Crain @ 2011-07-05 20:11 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: 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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-05 20:13 Kevin Crain <kevin.crain1@gmail.com>
parent: Kevin Crain <kevin.crain1@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Kevin Crain @ 2011-07-05 20:13 UTC (permalink / raw)
To: sgendler@ideasculptor.com; johnf@jfcomputer.com; +Cc: 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
>>
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-05 21:42 John Fabiani <johnf@jfcomputer.com>
parent: Kevin Crain <kevin.crain1@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: John Fabiani @ 2011-07-05 21:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-06 00:41 Samuel Gendler <sgendler@ideasculptor.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2011-07-06 00:41 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-06 01:00 Kevin Crain <kevin.crain1@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kevin Crain @ 2011-07-06 01:00 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: John Fabiani <johnf@jfcomputer.com>; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-06 09:47 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: John Fabiani <johnf@jfcomputer.com>
1 sibling, 0 replies; 10+ messages in thread
From: Gavin Flower @ 2011-07-06 09:47 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
On 06/07/11 01:52, John Fabiani 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.
>
> Johnf
>
Hi John,
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?
(I've included the code to test the idea and the results, I am using
9.1beta2, but it should not make any difference - I think!):
DROP TABLE IF EXISTS my_order;
DROP TABLE IF EXISTS order_sequence;
CREATE TABLE my_order
(
order_num text PRIMARY KEY,
payload text
);
CREATE TABLE order_sequence
(
type int,
day date,
seq int NOT NULL,
PRIMARY KEY (type, day)
);
CREATE OR REPLACE FUNCTION create_my_order
(
IN type int,
IN day date,
IN payload text
) RETURNS VOID
AS
$$
DECLARE
v_order_num text;
v_seq_old int;
v_seq_new int;
BEGIN
SELECT
os.seq
FROM
order_sequence os
WHERE
os.type = create_my_order.type AND
os.day = create_my_order.day
INTO
v_seq_old;
IF v_seq_old IS NULL THEN
v_seq_new := 1;
INSERT INTO order_sequence(type, day, seq)
VALUES (type, day, v_seq_new);
ELSE
v_seq_new := v_seq_old + 1;
UPDATE
order_sequence AS os
SET
seq = v_seq_new
WHERE
os.type = create_my_order.type AND
os.day = create_my_order.day;
END IF;
v_order_num := type::text ||
'-' ||
to_char(day, 'YYMMDD') ||
'-' ||
v_seq_new::text;
INSERT INTO my_order(order_num, payload)
VALUES (v_order_num, payload);
END;
$$ LANGUAGE plpgsql
VOLATILE
;
SELECT create_my_order (0, '2010-03-24', 'order #1 details');
SELECT create_my_order (0, '2010-03-24', 'order #2 details');
SELECT create_my_order (0, '2010-06-15', 'order #3 details');
SELECT create_my_order (5, '2010-03-24', 'order #4 details');
SELECT create_my_order (0, '2010-06-15', 'order #5 details');
SELECT create_my_order (3, '2010-06-14', 'order #6 details');
TABLE order_sequence;
TABLE my_order;
////////// This outputs the following:
type | day | seq
------+------------+-----
0 | 2010-03-24 | 2
5 | 2010-03-24 | 1
0 | 2010-06-15 | 2
3 | 2010-06-14 | 1
(4 rows)
order_num | payload
------------+------------------
0-100324-1 | order #1 details
0-100324-2 | order #2 details
0-100615-1 | order #3 details
5-100324-1 | order #4 details
0-100615-2 | order #5 details
3-100614-1 | order #6 details
(6 rows)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-06 12:28 Jasen Betts <jasen@xnet.co.nz>
parent: Kevin Crain <kevin.crain1@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jasen Betts @ 2011-07-06 12:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: interesting sequence
@ 2011-07-13 04:57 Kevin Crain <kevin.crain1@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 10+ messages in thread
From: Kevin Crain @ 2011-07-13 04:57 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-07-13 04:57 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-05 13:52 interesting sequence John Fabiani <johnf@jfcomputer.com>
2011-07-05 19:38 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-07-05 20:11 ` Kevin Crain <kevin.crain1@gmail.com>
2011-07-05 20:13 ` Kevin Crain <kevin.crain1@gmail.com>
2011-07-05 21:42 ` John Fabiani <johnf@jfcomputer.com>
2011-07-06 00:41 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-07-06 01:00 ` Kevin Crain <kevin.crain1@gmail.com>
2011-07-06 12:28 ` Jasen Betts <jasen@xnet.co.nz>
2011-07-13 04:57 ` Kevin Crain <kevin.crain1@gmail.com>
2011-07-06 09:47 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox