Re: tab_to_sting
От | Ramesh T |
---|---|
Тема | Re: tab_to_sting |
Дата | |
Msg-id | CAK8Zd=sSHrrzLHzmruZ1XNMUAdr31esO79HMYOhBXBMJwv1PGw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: tab_to_sting (Ramesh T <rameshparnanditech@gmail.com>) |
Список | pgsql-general |
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@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,JAMESthis function i need to run run in postgres. i think t_varchar2_tabis 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 ,rameshOn Thu, Jul 24, 2014 at 2:11 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:To help with getting answers, it would be helpful if you told the list what the Oracle function does or point to the documentation: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..?
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.klaver@aklaver.com
В списке pgsql-general по дате отправления: