SELECT tab_to_largestring(cast(array_agg(dhar_id)as t_varchar2_tab),':')FROM qa.dhar
when i run above statement it's return can't convert bigint to the t_varchar2_tab for the above function,i think problem at the t_varchar2_tab .. please any help..?runs on postgres 9.3 thanks in advance, ramesh On Thu, Jul 24, 2014 at 6:24 PM, Ramesh T <rameshparnandit...@gmail.com> 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.. > thanks in advance , > ramesh > > On Thu, Jul 24, 2014 at 2:11 AM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 07/23/2014 09:12 AM, Ramesh T wrote: >> >>> Hi, >>> >>> SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS >>> t_varchar2_tab)) AS employees >>> FROM hr.customer >>> >>> when i run function for table column values to single row function >>> name is hr.tab_to_largestring >>> >>> this code from oracle >>> >>> it return like function collect(character varying) does not exit >>> >>> please let me know in postgres collect () key is thier..? >>> >>> >>> and how to run this function..? >>> >> >> To help with getting answers, it would be helpful if you told the list >> what the Oracle function does or point to the documentation: >> >> http://docs.oracle.com/cd/E11882_01/server.112/e26088/ >> functions031.htm#SQLRF51285 >> >> Another option would be to investigate EnterpriseDB as they have an >> Oracle compatibility layer available: >> >> http://www.enterprisedb.com/ >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > >
