help needs in converting db2 function in postgresql.
От | Amar Dhole |
---|---|
Тема | help needs in converting db2 function in postgresql. |
Дата | |
Msg-id | B290BFEC59278744B17A7A3CB14307E9038726F0@NA-PA-VBE04.na.tibco.com обсуждение исходный текст |
Ответ на | Re: create role (Adrian Klaver <adrian.klaver@gmail.com>) |
Ответы |
Re: help needs in converting db2 function in postgresql.
|
Список | pgsql-sql |
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 -- toprevent a warning condition for infinite -- recursions, we add the explicit upper --boundary for the "ordinal" values WHERE ordinal < 10000 AND -- terminate if thereare no further delimiters -- remaining LOCATE(',', string, index+1) <> 0 ) SELECTordinal, 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 NOEXTERNAL 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 joinbelow 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 ) ;
В списке pgsql-sql по дате отправления: