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
>
