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

<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>