2011/1/11 Amar Dhole <adh...@tibco.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 "ordinal" 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, ',') $$; fi...@filip=# select * from instrtbl( 'one, two, really long three' ); instrtbl -------------------- one two really long three (3 rows) I love PostgreSQL. Filip