Re: tab_to_sting

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