Re: help needs in converting db2 function in postgresql. - Mailing list pgsql-sql

From Amar Dhole
Subject Re: help needs in converting db2 function in postgresql.
Date
Msg-id B290BFEC59278744B17A7A3CB14307E9038728DD@NA-PA-VBE04.na.tibco.com
Whole thread Raw
In response to Re: help needs in converting db2 function in postgresql.  (Filip Rembiałkowski <plk.zuber@gmail.com>)
Responses Help needed in skipping column for copy command
List pgsql-sql

Thanks this solves my problem..

 


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.

 

 

2011/1/11 Amar Dhole <adhole@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, ',')
$$;

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

I love PostgreSQL.

Filip

pgsql-sql by date:

Previous
From: Filip Rembiałkowski
Date:
Subject: Re: help needs in converting db2 function in postgresql.
Next
From: -
Date:
Subject: Implement PostgreSQL full text search