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
