Re: Convert Oracle function to PostgreSQL

SHARMILA JOTHIRAJAH wrote:
> 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?

Sorry, but we can't easily do that as complicated in PostgreSQL.

You'll have to live with something like

SELECT * FROM regexp_split_to_table('abc, xyz, 012', ', ?');
regexp_split_to_table
-----------------------
abc
xyz
012
(3 rows)

Yours,
Laurenz Albe