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

On 3/17/2010 9:52 AM, Ignacio Balcarce wrote: <blockquote
cite="mid:000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Wingdings;panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face{font-family:Wingdings;panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
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";}
span.EstiloCorreo17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
.MsoChpDefault{mso-style-type:export-only;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1{page:Section1;}/* List Definitions */@list
l0{mso-list-id:1067802487;mso-list-type:hybrid;mso-list-template-ids:1065240002-1447672184 201981977 201981979
201981967201981977 201981979 201981967 201981977 201981979;}
 
@list l0:level1{mso-level-text:%1-;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level2{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level3{mso-level-number-format:roman-lower;mso-level-tab-stop:none;mso-level-number-position:right;text-indent:-9.0pt;}
@list l0:level4{mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level5{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level6{mso-level-number-format:roman-lower;mso-level-tab-stop:none;mso-level-number-position:right;text-indent:-9.0pt;}
@list l0:level7{mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level8{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list l1{mso-list-id:1376933343;mso-list-type:hybrid;mso-list-template-ids:996162576 307771808 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l1:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:-;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:"Calibri","sans-serif";mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
@list l2{mso-list-id:1798378956;mso-list-type:hybrid;mso-list-template-ids:-914604966 2037692506 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l2:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:\F06E;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:Wingdings;mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
@list l3{mso-list-id:1934242401;mso-list-type:hybrid;mso-list-template-ids:-1502859232 -129998872 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l3:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:-;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:"Calibri","sans-serif";mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
ol{margin-bottom:0cm;}
ul{margin-bottom:0cm;}
--> </style><div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi all,</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I am facing a problem trying to convert from MSSQL
procedureto PostgreSQL function. </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color: blue;">CREATE</span><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">PROCEDURE</span> dbo<span
style="color:gray;">.</span>THUBAN_SP_GENERATEID</span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";"> </span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">         @NEWID <span style="color: blue;">VARCHAR</span><span
style="color:gray;">(</span>20<span style="color: gray;">)</span>  <span style="color: blue;">OUTPUT</span></span><p
class="MsoNormal"style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
blue;">AS</span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New";
color:blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family:
"CourierNew"; color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size:
10pt;font-family: "Courier New";">        <span style="color: blue;">SET</span> @NEWID <span style="color:
gray;">=</span><span style="color: gray;">(</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">                        <span style="color: blue;">SELECT</span>
<spanstyle="color: fuchsia;">REPLACE</span><span style="color: gray;">(</span><span style="color:
fuchsia;">SUBSTRING</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">CONVERT</span><span
style="color:gray;">(</span><span style="color: blue;">CHAR</span><span style="color: gray;">(</span>10<span
style="color:gray;">),</span><span style="color: fuchsia;">GETDATE</span><span style="color: gray;">(),</span>20 <span
style="color:gray;">),</span>1<span style="color: gray;">,</span>10<span style="color: gray;">),</span><span
style="color:red;">'-'</span><span style="color: gray;">,</span><span style="color: red;">''</span><span style="color:
gray;">)</span></span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";">                               <span style="color: gray;">+</span> <span style="color:
fuchsia;">CAST</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">REPLICATE</span><span
style="color:gray;">(</span>0<span style="color: gray;">,</span>8<span style="color: gray;">-</span><span style="color:
fuchsia;">LEN</span><span style="color: gray;">(</span><span style="color: fuchsia;">ISNULL</span><span style="color:
gray;">(</span><spanstyle="color: fuchsia;">CAST</span><span style="color: gray;">(</span><span style="color:
fuchsia;">SUBSTRING</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">MAX</span><span style="color:
gray;">(</span>SEQ_ID<spanstyle="color: gray;">),</span>9<span style="color: gray;">,</span>8<span style="color:
gray;">)</span><span style="color: blue;">AS</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">INTEGER<span style="color: gray;">),</span>0<span style="color:
gray;">)</span><span style="color: gray;">+</span> 1<span style="color: gray;">))</span> <span style="color:
blue;">AS</span><span style="color: blue;">VARCHAR</span><span style="color: gray;">)</span></span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                                       
<spanstyle="color: gray;">+</span> <span style="color: fuchsia;">CAST</span><span style="color: gray;">(</span><span
style="color:fuchsia;">ISNULL</span><span style="color: gray;">(</span><span style="color: fuchsia;">CAST</span><span
style="color:gray;">(</span><span style="color: fuchsia;">SUBSTRING</span><span style="color: gray;">(</span><span
style="color:fuchsia;">MAX</span><span style="color: gray;">(</span>SEQ_ID<span style="color: gray;">),</span>9<span
style="color:gray;">,</span>8<span style="color: gray;">)</span> <span style="color: blue;">AS</span> INTEGER<span
style="color:gray;">),</span>0<span style="color: gray;">)</span> <span style="color: gray;">+</span> 1  <span
style="color:blue;">AS</span></span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: blue;">VARCHAR</span><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: gray;">)</span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family:
"CourierNew";">                        <span style="color: blue;">FROM</span> THUBAN_SEQ</span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                        <span
style="color:blue;">WHERE</span> <span style="color: fuchsia;">SUBSTRING</span><span style="color:
gray;">(</span>SEQ_ID<spanstyle="color: gray;">,</span>1<span style="color: gray;">,</span>8<span style="color:
gray;">)=</span></span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: fuchsia;">REPLACE</span><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
fuchsia;">SUBSTRING</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
fuchsia;">CONVERT</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color: blue;">CHAR</span><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New"; color: gray;">(</span><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">10<span style="color: gray;">),</span><span style="color:
fuchsia;">GETDATE</span><spanstyle="color: gray;">(),</span>20 <span style="color: gray;">),</span>1<span style="color:
gray;">,</span>10<spanstyle="color: gray;">),</span><span style="color: red;">'-'</span><span style="color:
gray;">,</span><spanstyle="color: red;">''</span><span style="color: gray;">)</span></span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                        <span
style="color:gray;">)</span></span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size:
10pt;font-family: "Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">        <span style="color: blue;">INSERT</span> <span
style="color:blue;">INTO</span> THUBAN_SEQ <span style="color: blue;">VALUES</span> <span style="color:
gray;">(</span>@NEWID<spanstyle="color: gray;">)</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New";">        <span style="color: blue;">SELECT</span>
@NEWID<span style="color: blue;">AS</span> ITEM_ID<span style="color: gray;">;</span></span><p class="MsoNormal"><span
style="font-size:10pt; font-family: "Courier New";">GO</span><p class="MsoNormal"><span style="font-size: 10pt;
font-family:"Courier New";"> </span><span lang="EN-US"></span></div></blockquote><br /><br /> I surprised this works in
MSSQL<br/><br /> CREATE SEQUENCE THUBAN_SEQ<br />   INCREMENT 1<br />   MINVALUE 1<br />   MAXVALUE
9223372036854775807<br/>   START 1<br />   CACHE 1;<br /><br /><br /> Now for the function to generate the ID with the
dateleading <br /><br /><p class="MsoNormal"><span lang="EN-US">CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()<br
/></span><spanlang="EN-US">RETURNS VARCHAR</span><p class="MsoNormal"><span lang="EN-US">AS $$<br /></span><span
lang="EN-US"></span><spanlang="EN-US">BEGIN</span><br /><br /> --now we get the next value from the thuban_seq and add
thedate to the  front.  <br /><br /> return  to_char( current_timestamp,   'MMDDYYYY')::varchar ||
nextval('THUBAN_SEQ')::varchar<br/><p class="MsoNormal"><span lang="EN-US">                </span><p
class="MsoNormal"><spanlang="EN-US">RETURN NEWID;</span><p class="MsoNormal"><span lang="EN-US">END;</span><span
lang="EN-US">$$LANGUAGE plpgsql;</span><br /><br /> If this is not what your after you need to give more information
whatyou want to accomplish <br /><br /><br /><br /><br /> All legitimate Magwerks Corporation quotations are sent in a
.PDFfile attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any
otherform of communication will not be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail, including
attachments,may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and
isintended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended
recipientor authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution
orcopying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by
replyingto this message and destroy all occurrences of this e-mail immediately. <br /> Thank you. <br />