Re: [SQL] help needs in converting db2 function in postgresql.

[Amar Dhole]

Thanks this solves my problem..

 

________________________________

From: filip.rembialkow...@gmail.com [mailto:filip.rembialkow...@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.

 

 

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 &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, ',')
$$;

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

I love PostgreSQL.

Filip