HI,
when i use string_agg function it returns
string_agg(bigint) does'nt exist.
when "array_string" function it's return can't convert bigint
to the t_varchar2_tab.
i have question t_varcha2_tab type is available on postgres 9.3..?
i need it please let me know
thanks in advance,
On Thu, Jul 24, 2014 at 7:26 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> 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.kla...@aklaver.com
>
