Re: interesting sequence - Mailing list pgsql-sql

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