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 )
;

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

2011/1/11 Amar Dhole <adhole (AT) tibco (DOT) com>


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)

I love PostgreSQL.

Filip

Thanks this solves my problem..



________________________________

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

I love PostgreSQL.

Filip