MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
| От | Ignacio Balcarce |
|---|---|
| Тема | MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date |
| Дата | |
| Msg-id | 000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com обсуждение исходный текст |
| Ответы |
Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date
(silly sad <sad@bankir.ru>)
Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date (silly sad <sad@bankir.ru>) Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date (Justin Graf <justin@magwerks.com>) |
| Список | 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>
В списке pgsql-sql по дате отправления: