From: filip.rembialkowski@gmail.com [mailto:filip.rembialkowski@gmail.com] On Behalf Of Filip Rembialkowski Sent: Wednesday, January 12, 2011 1:41 AM To: Amar Dhole Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] help needs in converting db2 function in postgresql.
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, ',') $$;
filip@filip=# select * from instrtbl( 'one, two, really long three' ); instrtbl -------------------- one two really long three (3 rows)