Thread: Convert Oracle function to PostgreSQL

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@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@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

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@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@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

On 2009-04-06, SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> wrote:

> Hi,
> I use this Oracle function(from  AskTom  -
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425) 

> ops$tkyte@dev8i> create or replace
>      function in_list( p_string in varchar2 ) return myTableType

> How can I convert this function into PostgreSQL ? Any thoughts?
> Thanks

I don't think postgres has table variables, but for this task you
can use a set-returning function.

I'm returning a set of text, but you can create composite types and
return them if needed.

where I've added stuff to your code I've used UPPERCASE

create or replace function in_list( p_string text ) RETURNS SETOF TEXT
as $F$
 DECLARE
  l_string   TEXT := p_string || ',';
  n          INT;
  begin
     loop
        n := POSITION( ',' IN l_string );
        IF n < 1
        THEN
          RETURN;
        END IF;
        RETURN NEXT TRIM(  SUBSTRING ( l_string FOR n-1 ) );
        l_string := substr( l_string, n+1 );
     end loop;
  end;
$F$ LANGUAGE PLPGSQL STRICT;

> ops$tkyte@dev8i> select *
>   2    from THE
>         ( select cast( in_list('abc, xyz, 012') as
>                               mytableType ) from dual ) a

select * from in_list('abc, xyz, 012') ;

It'd be interesting to contrast a PL_PYTHON solution, it's probably a
two-liner in python :)