Re: tab_to_sting

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 <rameshparnanditech(at)gmail(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>
>
>