| From: | SHARMILA JOTHIRAJAH <sharmi_jo(at)yahoo(dot)com> | 
|---|---|
| To: | General postgres mailing list <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Convert Oracle function to PostgreSQL | 
| Date: | 2009-04-06 17:08:39 | 
| Message-ID: | 622584.78734.qm@web110713.mail.gq1.yahoo.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi,
I use this Oracle function(from  AskTom  - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425) 
SQL> create or replace type myTableType as table 
     of varchar2 (255);
  2  /
 
Type created.
 
ops$tkyte(at)dev8i> create or replace 
     function in_list( p_string in varchar2 ) return myTableType
  2  as
  3      l_string        long default p_string || ',';
  4      l_data          myTableType := myTableType();
  5      n               number;
  6  begin
  7    loop
  8        exit when l_string is null;
  9        n := instr( l_string, ',' );
10         l_data.extend;
11         l_data(l_data.count) := 
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
12         l_string := substr( l_string, n+1 );
13    end loop;
14
15    return l_data;
16  end;
17  /
 
Function created.
 
ops$tkyte(at)dev8i> select *
  2    from THE 
        ( select cast( in_list('abc, xyz, 012') as
                              mytableType ) from dual ) a
  3  /
 
COLUMN_VALUE
------------------------
abc
xyz
012
How can I convert this function into PostgreSQL ? Any thoughts?
Thanks
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Emanuel Calvo Franco | 2009-04-06 17:35:05 | Re: [COMMITTERS] | 
| Previous Message | eehab hamzeh | 2009-04-06 16:32:38 |