Re: tab_to_sting - Mailing list pgsql-general

On 07/24/2014 05:54 AM, Ramesh T wrote:

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
     /

     CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                               p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
       l_string     VARCHAR2(32767);
     BEGIN
       FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
         IF i != p_varchar2_tab.FIRST THEN
           l_string := l_string || p_delimiter;
         END IF;
         l_string := l_string || p_varchar2_tab(i);
       END LOOP;
       RETURN l_string;
     END tab_to_string;
     /

The query below shows the COLLECT function in action.

     COLUMN employees FORMAT A50

     SELECT deptno,
            tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
     FROM   emp
     GROUP BY deptno;

         DEPTNO EMPLOYEES
     ---------- --------------------------------------------------
             10 CLARK,KING,MILLER
             20 SMITH,JONES,SCOTT,ADAMS,FORD
             30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

this function i need to run run in postgres. i think t_varchar2_tab
is the create type in oracle ,we need to replace t_varchar2_tab with
other type ..?

i need this one please let me know..

Well following Hubert's suggestion, here is a SO answer using the string_agg function that seems to apply.

thanks in advance ,
ramesh

--
Adrian Klaver
adrian.klaver@aklaver.com