Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date - Mailing list pgsql-sql

OOPS did not mean to click send <br /><br /> 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 date);<br /><br /> --insert a record ;<br /> insert into sequ_id  values (1,
'<spanstyle="font-size: 12pt; font-family: "Times New Roman","serif";">thuban_seq', current_date);<br /><br /><br />
---Now for the function to generate the ID with the date leading<br /><br />  CREATE OR REPLACE FUNCTION
THUBAN_SP_GENERATEID()<br/>  RETURNS VARCHAR<br /><br />  AS $$<br /> declare creturn varchar ;<br />     <br />
 BEGIN<br/> --now update the sequ_id table so we know the value we get makes 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 build the ID go to the table get the current value add some zeros in
frontand add the date to the  front. <br /><br />   </span><span style="font-size: 12pt; font-family: "Times New
Roman","serif";">creturn= </span><span style="font-size: 12pt; font-family: "Times New Roman","serif";"> 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";">wheresequ_name= </span> '<span style="font-size: 12pt; font-family: "Times New
Roman","serif";">thuban_seq'</span>)<br /><br /> --update the sequence table<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";">--return the value<br /> return creturn ;<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,
insideof the god awful code from before. <br /><br /></span><br /><br /> All legitimate Magwerks Corporation quotations
aresent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations
receivedvia any other form of communication will not be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail,
includingattachments, may contain legally privileged, confidential or other information proprietary to Magwerks
Corporationand is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is
notthe intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination,distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error,
pleasenotify the sender by replying to this message and destroy all occurrences of this e-mail immediately. <br />
Thankyou. <br />