Home > mailing lists
converting type and function in postgresql ? - Mailing list pgsql-sql

From	Amar Dhole
Subject	converting type and function in postgresql ?
Date	December 21, 2010 10:47:38
Msg-id	B290BFEC59278744B17A7A3CB14307E9037613B8@NA-PA-VBE04.na.tibco.com Whole thread Raw
Responses	Re: converting type and function in postgresql ?
List	pgsql-sql
Tree view
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am new to postgesql.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have oracle type and function, which I needs to converting it into postgresql .</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am using postgresql 9.x</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Type : </span></font><p class="MsoNormal" style="text-autospace:none"><font color="#0000f0"
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
color:#0000F0">CREATE</span></font><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:black"> </span></font><font color="#0000f0" face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New";color:#0000F0">OR</span></font><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
color:black"> </span></font><font color="#0000f0" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:#0000F0">REPLACE</span></font><font color="black" face="Courier
New"size="2"><span style="font-size:10.0pt;font-family: 
"Courier New";color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="#0000f0"
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
color:#0000F0">TYPE</span></font><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:black"> INST</span></font><p class="MsoNormal"><font
color="#0000f0"face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New";color:#0000F0">AS</span></font><fontcolor="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:
"Courier New";color:black"> </span></font><font color="#0000f0" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
color:#0000F0">TABLE</span></font><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:black"> </span></font><font color="#0000f0" face="Courier New"
size="2"><spanstyle="font-size:10.0pt; 
font-family:"Courier New";color:#0000F0">OF</span></font><font color="black" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
color:black"> </span></font><font color="red" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:red">VARCHAR2</span></font><font color="#0000f0" face="Courier
New"size="2"><span style="font-size:10.0pt; 
font-family:"Courier New";color:#0000F0">(</span></font><font color="maroon" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
color:maroon">255</span></font><font color="#0000f0" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:#0000F0">)</span></font><p class="MsoNormal"><font
color="#0000f0"face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New";color:#0000F0"> </span></font><pclass="MsoNormal"><font color="#0000f0" face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew";color:#0000F0">How this type can be written in postgresql
??</span></font><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Oracle Function:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:#0000F0">CREATE</span></font><font color="black"
face="Courier"size="2"><span style="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">OR</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">REPLACE</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="olive" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:olive">function</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="olive" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:olive">in_list</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> p_string </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">in</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="red" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:red">varchar2</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">)</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">return</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> INST</span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">    </span></font><font color="#0000f0"
face="Courier"size="2"><span style="font-size: 
10.0pt;font-family:Courier;color:#0000F0">as</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">        l_string        </span></font><font
color="red"face="Courier" size="2"><span style="font-size:10.0pt;font-family:Courier;color:red">long</span></font><font
color="black"face="Courier" size="2"><span style="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">default</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> p_string || </span></font><font color="red" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:red">','</span></font><fontcolor="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">;</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black"></span></font><pclass="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black">       l_data          INST </span></font><font
color="#0000f0"face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">:=</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> INST</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">();</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">        n               </span></font><font
color="red"face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:red">number</span></font><fontcolor="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">;</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black"></span></font><pclass="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black">   </span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Courier;color:#0000F0">begin</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">      </span></font><font color="#0000f0"
face="Courier"size="2"><span style="font-size: 
10.0pt;font-family:Courier;color:#0000F0">loop</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">          </span></font><font color="#0000f0"
face="Courier"size="2"><span style="font-size: 
10.0pt;font-family:Courier;color:#0000F0">exit</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">when</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> l_string </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">is</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">null;</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">          n </span></font><font color="#0000f0"
face="Courier"size="2"><span style="font-size:10.0pt;font-family:Courier;color:#0000F0">:=</span></font><font
color="black"face="Courier" size="2"><span style="font-size:10.0pt;font-family:Courier; 
color:black"> instr</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> l_string</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">,</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="red" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:red">','</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">);</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">         l_data</span></font><font
color="#0000f0"face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">.extend;</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">         l_data</span></font><font
color="#0000f0"face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black">l_data</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">.</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black">count</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">)</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">:=</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> ltrim</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> rtrim</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> substr</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> l_string</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">,</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="maroon" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:maroon">1</span></font><fontcolor="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">,</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black">n</span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">-</span></font><font color="maroon" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:maroon">1</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">)</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">)</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">);</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black">         l_string </span></font><font
color="#0000f0"face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">:=</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> substr</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">(</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> l_string</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">,</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"> n</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">+</span></font><fontcolor="maroon" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:maroon">1</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black"></span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">);</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black"></span></font><pclass="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black">   </span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Courier;color:#0000F0">end</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"> </span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">loop;</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier;color:black"> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black">   </span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Courier;color:#0000F0">return</span></font><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;
color:black"> l_data</span></font><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0">;</span></font><fontcolor="black" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family:Courier; 
color:black"></span></font><p class="MsoNormal"><font color="black" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:black"> </span></font><font color="#0000f0" face="Courier"
size="2"><spanstyle="font-size:10.0pt;font-family: 
Courier;color:#0000F0">end;</span></font><p class="MsoNormal"><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0"> </span></font><pclass="MsoNormal"><font color="#0000f0"
face="Courier"size="2"><span style="font-size:10.0pt;font-family:Courier;color:#0000F0">how this function can be
rewrittenin postgresql ?</span></font><p class="MsoNormal"><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0"> </span></font><pclass="MsoNormal"><font color="#0000f0"
face="Courier"size="2"><span style="font-size:10.0pt;font-family:Courier;color:#0000F0">your help will help me a
lot.</span></font><pclass="MsoNormal"><font color="#0000f0" face="Courier" size="2"><span
style="font-size:10.0pt;font-family:Courier;color:#0000F0"> </span></font><pclass="MsoNormal"><font color="#0000f0"
face="Courier"size="2"><span style="font-size:10.0pt;font-family:Courier;color:#0000F0">Thanks<br /> Amar
</span></font><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"></span></font></div>
pgsql-sql by date:
From: Pavel Stehule
Date: 21 December 2010, 10:37:47
Subject: Re: data import
From: Trinath Somanchi
Date: 21 December 2010, 10:58:09
Subject: Re: converting type and function in postgresql ?
converting type and function in postgresql ? - Mailing list pgsql-sql

Previous

Next
