Postgres Pro
Downloads
Home   >   mailing lists

Thread: Needs Function

Needs Function

From
"Penchalaiah P."
Date:
<img class="shape" height="0" src="cid:image001.jpg@01C78CCF.0463D980" style="display:none;width:0;height:0"
v:shapes="_x0000_Mail"v:src="cid:image001.jpg@01C78CCF.0463D980" width="0" /><div class="Section1"><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt;color:black">I need a user defined function for the following
purpose….</span></font><pclass="MsoNormal"><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black">IfI pass a string with comma ( </span></font><b><font color="black" size="5"><span
style="font-size:16.0pt;color:black;font-weight:bold">,</span></font></b><font color="black"><span
style="color:black">)separated chars/values…. It should be appear in next line…</span></font><p class="MsoNormal"><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black">Ex: select get_sep_str (‘SK,
rp,h, j, 6, 9, kl’) from dual;</span></font><p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt;color:black">Output :</span></font><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">SK</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">rp</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">h </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">j </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">6 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">9 </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">kl</span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold"> </span></font></b><p class="MsoNormal" style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.45pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">(Or)</span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font color="black"
face="TimesNew Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold">I have one table like this…..</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><u><font
color="red"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:red; 
font-weight:bold">Temp Table:</span></font></u></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><u><fontcolor="red" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:red;
font-weight:bold"><span style="text-decoration:none"> </span></span></font></u></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold">Deptno number(10)</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold">Empno  varchar2(200);</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><u><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold">Data in temp table:</span></font></u></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold">Deptno                       Empno</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold">-----------                      ------------</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list:
l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold"><span style="mso-list:Ignore">10<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New
Roman"">                                     </span></font></span></span></font></b><b><font color="black"><span
style="color:black;font-weight:bold">B3091,B3092,B3093,B3085</span></font></b><pclass="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list:
l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold"><span style="mso-list:Ignore">11<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New
Roman"">                                     </span></font></span></span></font></b><b><font color="black"><span
style="color:black;font-weight:bold">3651,6521</span></font></b><pclass="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:124.5pt;margin-bottom:.0001pt;text-indent:-106.5pt;mso-list:
l0 level1 lfo2"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold"><span style="mso-list:Ignore">12<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New
Roman"">                                     </span></font></span></span></font></b><b><font color="black"><span
style="color:black;font-weight:bold">H3062</span></font></b><pclass="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold">Now, I want to display the data like this…..</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold"> </span></font></b><p class="MsoNormal" style="margin:0in;margin-bottom:.0001pt"><b><font
color="black"face="Times New Roman" size="3"><span style="font-size:12.0pt;color:black; 
font-weight:bold">Deptno                       Empno</span></font></b><p class="MsoNormal"
style="margin:0in;margin-bottom:.0001pt"><b><fontcolor="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;
font-weight:bold">---------                        ---------</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">10                   B3091 </span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">10                   B3092</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">10                   B3093 </span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">10                   B3094</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">11                   3651</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">11                   6521</span></font></b><p class="MsoNormal"
style="mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
0in;margin-left:37.5pt;margin-bottom:.0001pt"><b><font color="black" face="Times New Roman" size="3"><span
style="font-size:12.0pt;color:black;font-weight:
bold">12                   H3062</span></font></b><p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><spanstyle="font-size:12.0pt;color:black">Now, how can I achieve this….</span></font><p><font color="#006666"
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial;color:#006666"> </span></font></div><table><tr><td bgcolor="#ffffff"><font
color="#000000">Informationtransmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its
Customersand is intended for use only by the individual or the entity to which it is addressed, and may contain
informationthat is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended
recipientor it appears that this mail has been forwarded to you without proper authority, you are notified that any use
ordissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately
atinfo.in@infics.com and delete this email from your records.<br /></font></td></tr></table>

Re: Needs Function

From
"Rodrigo De León"
Date:
On 5/2/07, Penchalaiah P. <penchalaiahp@infics.com> wrote:
> I need a user defined function for the following purpose….
>
> If I pass a string with comma ( , ) separated chars/values…. It should be appear in next line…
>
> Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
>
> Output :
>
> SK
>
> rp
>
> h
>
> j
>
> 6
>
> 9
>
> kl
>
>
>
> (Or)
>
>
>
> I have one table like this…..
>
>
>
> Temp Table:
>
>
>
> Deptno number(10)
>
> Empno  varchar2(200);
>
>
>
> Data in temp table:
>
>
>
> Deptno                       Empno
>
> -----------                      ------------
>
> 10                                      B3091,B3092,B3093,B3085
>
> 11                                      3651,6521
>
> 12                                      H3062
>
>
>
> Now, I want to display the data like this…..
>
>
>
> Deptno                       Empno
>
> ---------                        ---------
>
> 10                   B3091
>
> 10                   B3092
>
> 10                   B3093
>
> 10                   B3094
>
> 11                   3651
>
> 11                   6521
>
> 12                   H3062
>
> Now, how can I achieve this….

See:

http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php

Re: Needs Function

From
"Aaron Bono"
Date:
On 5/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
Man, where was this last week when I needed it.  I will have to keep this for future reference.

Thanks!

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

Re: Needs Function

From
"Bart Degryse"
Date:
Well, actually it was there last week too :)

>>> "Aaron Bono" <postgresql@aranya.com> 2007-05-03 16:14 >>>
On 5/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
Man, where was this last week when I needed it.  I will have to keep this for future reference.

Thanks!

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================