Re: help needs in converting db2 function in postgresql.

2011/1/11 Amar Dhole <adhole(at)tibco(dot)com>

> Hi,
> I need helping converting following db2 function in postgresql function.
> Any pointer will be great help in proceeding me ahead.
>
> CREATE FUNCTION in_liststring ( string CLOB(64K) )
> RETURNS TABLE ( ordinal INTEGER, index INTEGER )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( VALUES ( 0, 0 )
> UNION ALL
> SELECT ordinal+1, COALESCE(NULLIF(
> -- find the next delimiter ','
> LOCATE(',', string, index+1), 0),
> LENGTH(string)+1)
> FROM t
> -- to prevent a warning condition for infinite
> -- recursions, we add the explicit upper
> -- boundary for the &quot;ordinal&quot; values
> WHERE ordinal < 10000 AND
> -- terminate if there are no further delimiters
> -- remaining
> LOCATE(',', string, index+1) <> 0 )
> SELECT ordinal, index
> FROM t
> UNION ALL
> -- add indicator for the end of the string
> SELECT MAX(ordinal)+1, LENGTH(string)+1
> FROM t
> ;
>
> commit;
>
> DROP FUNCTION INSTRTBL;
>
> CREATE FUNCTION INSTRTBL ( string CLOB(64K) )
> RETURNS TABLE ( INSTRTBL CLOB(64K) )
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
> RETURN
> WITH t(ordinal, index) AS
> ( SELECT ordinal, index
> FROM TABLE ( in_liststring(string) ) AS x )
> SELECT SUBSTR(string, t1.index+1, t2.index - t1.index - 1)
> -- the join below makes sure that we have the lower and
> -- upper index where we can find each of the ',' delimiters
> -- that are separating the INSTRTBL. (For this, we exploit
> -- the additional indexes pointing to the beginning and end
> -- of the string.)
> FROM t AS t1 JOIN t AS t2 ON
> ( t2.ordinal = t1.ordinal+1 )
> ;
>
>

create or replace function instrtbl(text)
returns table(instrtbl text)
language sql
immutable
strict
as $$
SELECT * FROM regexp_split_to_table($1, ',')
$$;

filip(at)filip=# select * from instrtbl( 'one, two, really long three' );
instrtbl
--------------------
one
two
really long three
(3 rows)

I love PostgreSQL.

Filip