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