Обсуждение: converting type and function in postgresql ?
<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>
http://ora2pg.darold.net/
Hi,
I am new to postgesql.
I have oracle type and function, which I needs to converting it into postgresql .
I am using postgresql 9.x
Type :
CREATE OR REPLACE
TYPE INST
AS TABLE OF VARCHAR2(255)
How this type can be written in postgresql ??
Oracle Function:
CREATE OR REPLACE function in_list ( p_string in varchar2 ) return INST
as
l_string long default p_string || ',';
l_data INST := INST();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ',' );
l_data.extend;
l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end;
how this function can be rewritten in postgresql ?
your help will help me a lot.
Thanks
Amar
--
Regards,
----------------------------------------------
Trinath Somanchi,