From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: pgsql-sql@postgresql.org
Subject: interesting sequence
Date: Tue, 5 Jul 2011 06:52:22 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34.8-0.2-desktop; KDE/4.4.4; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201107050652.22187.johnf@jfcomputer.com>

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