Re: Convert Oracle function to PostgreSQL

[Jasen Betts <jasen@xxxxxxxxxx>]

On 2009-04-06, SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx> 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 :)

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general