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