Thread: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
<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="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">CREATE</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span style="color:blue">PROCEDURE</span>dbo<span style="color:gray">.</span>THUBAN_SP_GENERATEID</span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> </span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;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="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">AS</span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">SET</span> @NEWID <span style="color:gray">=</span><span style="color:gray">(</span></span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span style="color:blue">SELECT</span> <span style="color:fuchsia">REPLACE</span><span style="color:gray">(</span><spanstyle="color:fuchsia">SUBSTRING</span><span style="color:gray">(</span><span style="color:fuchsia">CONVERT</span><spanstyle="color:gray">(</span><span style="color:blue">CHAR</span><span style="color:gray">(</span>10<spanstyle="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><spanstyle="color:red">'-'</span><span style="color:gray">,</span><span style="color:red">''</span><spanstyle="color:gray">)</span></span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span style="color:gray">+</span> <span style="color:fuchsia">CAST</span><span style="color:gray">(</span><spanstyle="color:fuchsia">REPLICATE</span><span style="color:gray">(</span>0<span style="color:gray">,</span>8<spanstyle="color:gray">-</span><span style="color:fuchsia">LEN</span> <span style="color:gray">(</span><spanstyle="color:fuchsia">ISNULL</span><span style="color:gray">(</span><span style="color:fuchsia">CAST</span><spanstyle="color:gray">(</span><span style="color:fuchsia">SUBSTRING</span><span style="color:gray">(</span><spanstyle="color:fuchsia">MAX</span><span style="color:gray">(</span>SEQ_ID<span style="color:gray">),</span>9<spanstyle="color:gray">,</span>8<span style="color:gray">)</span> <span style="color:blue">AS</span></span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew"">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="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span style="color:gray">+</span> <span style="color:fuchsia">CAST</span><spanstyle="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> 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="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">VARCHAR</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray">)</span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">FROM</span> THUBAN_SEQ</span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:blue">WHERE</span> <span style="color:fuchsia">SUBSTRING</span><spanstyle="color:gray">(</span>SEQ_ID<span style="color:gray">,</span>1<span style="color:gray">,</span>8<spanstyle="color:gray">)=</span></span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:fuchsia">REPLACE</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:fuchsia">SUBSTRING</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:fuchsia">CONVERT</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">CHAR</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">10<span style="color:gray">),</span><spanstyle="color:fuchsia">GETDATE</span><span style="color:gray">(),</span>20 <span style="color:gray">),</span>1<spanstyle="color:gray">,</span>10<span style="color:gray">),</span><span style="color:red">'-'</span><spanstyle="color:gray">,</span><span style="color:red">''</span><span style="color:gray">)</span></span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew""> <span style="color:gray">)</span></span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew";color:gray"> </span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span style="color:blue">INSERT</span><span style="color:blue">INTO</span> THUBAN_SEQ <span style="color:blue">VALUES</span> <spanstyle="color:gray">(</span>@NEWID<span style="color:gray">)</span></span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray"> </span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;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><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"">GO</span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> </span><p class="MsoNormal"><spanlang="EN-US">This is what I made, </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><spanlang="EN-US">CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()</span><p class="MsoNormal"><span lang="EN-US">RETURNSVARCHAR</span><p class="MsoNormal"><span lang="EN-US">AS $$</span><p class="MsoNormal"><span lang="EN-US">DECLARENEWID VARCHAR;</span><p class="MsoNormal"><span lang="EN-US">DECLARE SEQID VARCHAR;</span><pclass="MsoNormal"><span lang="EN-US">BEGIN</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><spanlang="EN-US"> SELECT INTO NEWID TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD');</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> -- IF EXISTS A ROW INTHE TABLE STARTING WITH THE CURRENT_DATE, SELECT THE MAX OF THEM.</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> IF EXISTS(SELECT(MAX(SEQ_ID)) FROM THUBAN_SEQWHERE SEQ_ID LIKE (SELECT TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD') || '%')) THEN</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> SELECTINTO NEWID ((SELECT(MAX(SEQID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE NEWID || '%') + 1);</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span>ELSE<p class="MsoNormal"><span lang="EN-US"> -- THIS ISNOT RIGHT AT ALL, RIGHT? HOW CAN I DO TO CONCATENATE AN INTEGER NUMBER LIKE 14 + SOME NUMBER OF 0 BEFORE?</span><p class="MsoNormal"><spanlang="EN-US"> SEQID := '00000001';</span><p class="MsoNormal"><spanlang="EN-US"> NEWID := NEWID + SEQID;</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> END IF;</span><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><p class="MsoNormal"><spanlang="EN-US">$$ LANGUAGE plpgsql;</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><spanlang="EN-US">SELECT THUBAN_SP_GENERATEID();</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Beside this, there is something than I would like to ask thanI couldn’t find. How can I do to set a variable in a way like this as MSSQL does:</span><p class="MsoNormal" style="margin-left:36.0pt"><spanlang="EN-US"> </span><p class="MsoNormal" style="text-indent:18.0pt;text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">SET</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> @NEWID <span style="color:gray">=</span><span style="color:gray">(</span><span style="color:blue">SELECT…… </span></span><p class="MsoNormal"style="text-indent:18.0pt;text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span><p class="MsoNormal" style="text-indent:18.0pt;text-autospace:none"><spanlang="EN-US">And not doing SELECT INTO VARIABLE_TO_SET (SELECT…..</span><pclass="MsoNormal" style="text-indent:18.0pt;text-autospace:none"><span lang="EN-US"> </span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US">All comments will be welcome, I am pretty new with PostgreSQLbut I find It very interesting.</span><p class="MsoNormal" style="text-autospace:none"><span lang="EN-US"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US">Thanks & Regards,</span><p class="MsoNormal"style="text-autospace:none"><span lang="EN-US"> </span><p class="MsoNormal" style="text-autospace:none"><spanlang="EN-US"> </span><p class="MsoNormal" style="text-autospace:none"><span lang="EN-US">Ignacio</span></div>
On 03/17/10 17:52, Ignacio Balcarce wrote: > CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID > > @NEWID VARCHAR(20) OUTPUT > > AS > > SET @NEWID = ( > > SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') > > + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS > > INTEGER),0) + 1)) AS VARCHAR) > > + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS > > VARCHAR) > > FROM THUBAN_SEQ > > WHERE SUBSTRING(SEQ_ID,1,8)= > > REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','') > > ) > > INSERT INTO THUBAN_SEQ VALUES (@NEWID) > > SELECT @NEWID AS ITEM_ID; > > GO > At a first glance it looks like an INSERT INTO thuban_seq(seq_id) VALUES (your_strange_string_processing( now() )) RETURNING seq_id; But i couldn't interpret your extremely strange string processing with dates. Please, FIRST OF ALL, get rid of this unnecessary brainfuck, use postgres date-time arithmetic and clarify the idea of this routine.
On 03/17/10 17:52, Ignacio Balcarce wrote: > -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE Sorry, your field is not an atom => your database does not met a FIRST normal form. it needs normalization urgently.
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 />
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 />
Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
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 />
Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
Justin,
Thanks in advance for your email. I forgot to tell than everyday IDs must start from 0. So… sequence id would look like: YYYYMMDD 00000001, YYYYMMDD 00000002, etc.
Is there any way to make this sequence start from 0 every day?
Thanks & Regards,
Ignacio
De: Justin Graf [mailto:justin@magwerks.com]
Enviado el: Jueves, 18 de Marzo de 2010 02:02 p.m.
Para: Ignacio Balcarce
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
On 3/17/2010 9:52 AM, Ignacio Balcarce wrote:
Hi all,
I am facing a problem trying to convert from MSSQL procedure to PostgreSQL function.
CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID
@NEWID VARCHAR(20) OUTPUT
AS
SET @NEWID = (
SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
+ CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS
INTEGER),0) + 1)) AS VARCHAR)
+ CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS
VARCHAR)
FROM THUBAN_SEQ
WHERE SUBSTRING(SEQ_ID,1,8)=
REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
)
INSERT INTO THUBAN_SEQ VALUES (@NEWID)
SELECT @NEWID AS ITEM_ID;
GO
I surprised this works in MSSQL
CREATE SEQUENCE THUBAN_SEQ
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
Now for the function to generate the ID with the date leading
CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
RETURNS VARCHAR
AS $$
BEGIN
--now we get the next value from the thuban_seq and add the date to the front.
return to_char( current_timestamp, 'MMDDYYYY')::varchar || nextval('THUBAN_SEQ')::varchar
RETURN NEWID;
END;
$$ LANGUAGE plpgsql;
If this is not what your after you need to give more information what you want to accomplish
All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.
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 the individual to whom it addresses. If the reader of this e-mail is not the 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, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.