Re: Convert Oracle function to PostgreSQL

On 2009-04-06, SHARMILA JOTHIRAJAH <sharmi_jo(at)yahoo(dot)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(at)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(at)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 :)