Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

On 3/18/2010 12:53 PM, Ignacio Balcarce wrote: <blockquote
cite="mid:000b01cac6c3$f9f58520$ede08f60$@balcarce@vivatia.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph,
div.MsoListParagraph{mso-style-priority:34;margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
span.EstiloCorreo18{mso-style-type:personal;font-family:"Calibri","sans-serif";color:windowtext;}
span.EstiloCorreo19{mso-style-type:personal-reply;font-family:"Calibri","sans-serif";color:#1F497D;}
.MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><span style="color: rgb(31, 73, 125);">Justin,</span><p
class="MsoNormal"><spanstyle="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);">Thanks in advance for your email. I forgot to tell than everyday IDs must start from 0. So… sequence
idwould look like: YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73,
125);">00000001,YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73, 125);">00000002,
etc.</span><pclass="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span
lang="EN-US"style="color: rgb(31, 73, 125);">Is there any way to make this sequence start from 0 every day?</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US"
style="color:rgb(31, 73, 125);">Thanks & Regards,</span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);">Ignacio</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span>
---------------------------------------------<br/><p class="MsoNormal" style="margin-bottom: 12pt;"><span
style="font-size:12pt; font-family: "Times New Roman","serif";"><br /><br /></span></div></blockquote> --we need to
createa table so we keep track sequence number and when to reset the count<br /><br /> create table sequ_id ( id_number
int,sequ_name char(25), date_lastrun );<br /><br /> --insert a record ;<br /> insert into sequ_id  values (1, '<span
style="font-size:12pt; font-family: "Times New Roman","serif";">thuban_seq', current_date);<br /><br /><br />  Now for
thefunction to generate the ID with the date leading<br /><br />  CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()<br
/> RETURNS VARCHAR<br /><br />  AS $$<br /><br />  BEGIN<br /> --now update the sequ_id table so we know the value we
getmakes sense,<br /> Update sequ_id set id_number = 1 where sequ_name= </span> '<span style="font-size: 12pt;
font-family:"Times New Roman","serif";">thuban_seq' and date_lastrun</span> <> current_date;<br /><span
style="font-size:12pt; font-family: "Times New Roman","serif";"><br />  --now we get the next value from the thuban_seq
andadd the date to the  front. <br /><br />  return  to_char( current_date,   'YYYYMMDD')::varchar || ' ' || (Select
lpad(id_number::char, 7, '0' )::varchar from</span><span style="font-size: 12pt; font-family: "Times New
Roman","serif";">sequ_id</span><span style="font-size: 12pt; font-family: "Times New Roman","serif";"> </span><span
style="font-size:12pt; font-family: "Times New Roman","serif";">where sequ_name= </span> '<span style="font-size: 12pt;
font-family:"Times New Roman","serif";">thuban_seq' and date_lastrun</span>)<br /><br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";">Update sequ_id set id_number = (id_number + 1) where sequ_name= </span> '<span
style="font-size:12pt; font-family: "Times New Roman","serif";">thuban_seq';</span><br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";"><br /><br />  END;<br /> $$ LANGUAGE plpgsql;<br /><br /> this will do what you
want.<br/><br /> now i  have NOT  tested this but should get you closer, inside of the god awful code from before. <br
/><br/></span><br /><br /> All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a
uniqueID number generated by our proprietary quotation system. Quotations received via any other form of communication
willnot be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged,confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of
theindividual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the
readeris hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is
strictlyprohibited. If you have received this e-mail in error, please notify the sender by replying to this message and
destroyall occurrences of this e-mail immediately. <br /> Thank you. <br />