Thread: tab_to_sting
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..?
I don't know what collect actually does, but just guessing, I would say that you're looking for string_agg()
depesz
depesz
On Wed, Jul 23, 2014 at 6:12 PM, Ramesh T <rameshparnanditech@gmail.com> wrote:
Hi,SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS t_varchar2_tab)) AS employeesFROM hr.customerwhen i run function for table column values to single row function name is hr.tab_to_largestringthis code from oracleit return like function collect(character varying) does not exitplease let me know in postgres collect () key is thier..?and how to run this function..?
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.klaver@aklaver.com
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.klaver@aklaver.com
On 07/24/2014 05:54 AM, Ramesh T wrote: > > i need this one please let me know.. > thanks in advance , Previous post would have been better with the link :( http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query > ramesh -- Adrian Klaver adrian.klaver@aklaver.com
On 07/24/2014 07:03 AM, Ramesh T wrote: > 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..? No that is custom type. > > i need it please let me know > thanks in advance, > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/24/2014 07:11 AM, Ramesh T wrote: > hi , > i looked into that link ,when i run string_agg does not exist returns > ,But i'm using function here not paasing table to the function only i'm > passing column name and delimiter to the function from select statement > please look into the my first post.. What version of Postgres are you using? The query below should work: SELECT deptno, string_agg(employee, ',') FROM emp GROUP BY deptno; > thanks, > ram -- Adrian Klaver adrian.klaver@aklaver.com
On 07/24/2014 07:22 AM, Ramesh T wrote:
> SELECT
> qa.tab_to_largestringcheck(cast(array_agg(part_id)as
> t_varchar2_tab),':')FROM qa.part
>
> when i replace string_agg it's return does not exit,
My guess is if you look at the error message it is complaining about the
type of argument passed in.
Please show us the actual error message if that is not the case.
>
> need to enable string_agg ..?i think is predefined right
From psql:
production=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit
(1 row)
production=# \df string_agg
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | string_agg | bytea | bytea, bytea | agg
pg_catalog | string_agg | text | text, text | agg
(2 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com
On 07/24/2014 07:46 AM, Ramesh T wrote: > i ran the \df string_agg is their > > but retuns like > ERROR: function string_agg(character varying) does not exist > LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var... > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > Please, do not top post. Also try the query I sent you off-list: SELECT deptno, string_agg(employee, ',') FROM emp GROUP BY deptno; Forget about the t_varcha2_tab type. -- Adrian Klaver adrian.klaver@aklaver.com
On 07/24/2014 07:46 AM, Ramesh T wrote:
> i ran the \df string_agg is their
>
> but retuns like
> ERROR: function string_agg(character varying) does not exist
> LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var...
I should have explained what is going on here.
If you do:
production=# \df string_agg
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | string_agg | bytea | bytea, bytea | agg
pg_catalog | string_agg | text | text, text | agg
(2 rows)
you see that there are two variations of string_agg, one that accepts
text, text and the other that accepts bytea, bytea as input. When you
ran your function it was passing in a single character varying input to
string_agg. This is not a supported string_agg variation, so Postgres
reports it does not exist in that form. See the HINT below.
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.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
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
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.klaver@aklaver.com> wrote:
Well following Hubert's suggestion, here is a SO answer using the string_agg function that seems to apply.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..--thanks in advance ,
ramesh
Adrian Klaver
adrian.klaver@aklaver.com
SELECT
qa.tab_to_largestringcheck(cast(array_agg(part_id)as t_varchar2_tab),':')FROM qa.part
when i replace string_agg it's return does not exit,
need to enable string_agg ..?i think is predefined right
On Thu, Jul 24, 2014 at 7:47 PM, Ramesh T <rameshparnanditech@gmail.com> wrote:
postgres 9.3On Thu, Jul 24, 2014 at 7:46 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 07/24/2014 07:11 AM, Ramesh T wrote:What version of Postgres are you using?hi ,
i looked into that link ,when i run string_agg does not exist returns
,But i'm using function here not paasing table to the function only i'm
passing column name and delimiter to the function from select statement
please look into the my first post..
The query below should work:
SELECT deptno, string_agg(employee, ',')
FROM emp
GROUP BY deptno;thanks,
ram
--
Adrian Klaver
adrian.klaver@aklaver.com
i ran the \df string_agg is their
but retuns like
ERROR: function string_agg(character varying) does not exist
LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
On Thu, Jul 24, 2014 at 8:00 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 07/24/2014 07:22 AM, Ramesh T wrote:My guess is if you look at the error message it is complaining about the type of argument passed in.SELECT
qa.tab_to_largestringcheck(cast(array_agg(part_id)as
t_varchar2_tab),':')FROM qa.part
when i replace string_agg it's return does not exit,
Please show us the actual error message if that is not the case.From psql:
need to enable string_agg ..?i think is predefined right
production=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit
(1 row)
production=# \df string_agg
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | string_agg | bytea | bytea, bytea | agg
pg_catalog | string_agg | text | text, text | agg
(2 rows)
--
Adrian Klaver
adrian.klaver@aklaver.com
I have try
select string_agg(partname,':') from part_tab; its return same,
ERROR: function string_agg(bigint, unknown) does not exist
LINE 1: select string_agg(part_id,':') from part;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
i thought string_agg and array_agg same, is it right..?
On Thu, Jul 24, 2014 at 8:19 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 07/24/2014 07:46 AM, Ramesh T wrote:Please, do not top post.i ran the \df string_agg is their
but retuns like
ERROR: function string_agg(character varying) does not exist
LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
Also try the query I sent you off-list:Forget about the t_varcha2_tab type.
SELECT deptno, string_agg(employee, ',')
FROM emp
GROUP BY deptno;
--
Adrian Klaver
adrian.klaver@aklaver.com
On 07/24/2014 08:03 AM, Ramesh T wrote: > I have try > select string_agg(partname,':') from part_tab; its return same, > > ERROR: function string_agg(bigint, unknown) does not exist > LINE 1: select string_agg(part_id,':') from part; Try: select string_agg(part_id::text,':') from part; > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > i thought string_agg and array_agg same, is it right..? No: http://www.postgresql.org/docs/9.3/static/functions-aggregate.html array_agg(expression) any array of the argument type input values, including nulls, concatenated into an array string_agg(expression, delimiter) (text, text) or (bytea, bytea) same as argument types input values concatenated into a string, separated by delimiter > > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi,
when i ran below statement its working fine..
select string_agg(part_id::text,':') from part;
But,
SELECT tab_to_largeStringcheck(cast(string_agg(part_id::text,':')as t_varchar2_tab)) FROM part
when i ran like
SELECT
qa.tab_to_largeStringcheck(string_agg(part_id::text,':'))
FROM qa.part
its returnfunction( text)does'nt exist
let me know how solve issue..
thanks,
On Thu, Jul 24, 2014 at 10:42 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 07/24/2014 08:03 AM, Ramesh T wrote:Try:I have try
select string_agg(partname,':') from part_tab; its return same,
ERROR: function string_agg(bigint, unknown) does not exist
LINE 1: select string_agg(part_id,':') from part;
select string_agg(part_id::text,':') from part;No:^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
i thought string_agg and array_agg same, is it right..?
http://www.postgresql.org/docs/9.3/static/functions-aggregate.html
array_agg(expression) any array of the argument type input values, including nulls, concatenated into an array
string_agg(expression, delimiter) (text, text) or (bytea, bytea) same as argument types input values concatenated into a string, separated by delimiter
--
Adrian Klaver
adrian.klaver@aklaver.com
Attachment
Ramesh T wrote > Hi, > when i ran below statement its working fine.. > select string_agg(part_id::text,':') from part; > But, > SELECT tab_to_largeStringcheck(cast(string_agg(part_id::text,':')as > t_varchar2_tab)) FROM part > > > [image: Inline image 1] > > when i ran like > > SELECT > qa.tab_to_largeStringcheck(string_agg(part_id::text,':')) > FROM qa.part > its returnfunction( text)does'nt exist > let me know how solve issue.. > thanks, You really need to spend a day reading the PostgreSQL documentation, especially the parts on what functions and data types are available. There are many things that work in Oracle but not PostgreSQL simply because names are different. If you know what need to do you should be able to recognize the stuff in PostgreSQL that will accomplish the same goal. You may have to write custom functions too. You should also explore EDB and the Oracle compatibility stuff they have written. Note that "tabtolargestringcheck(text)" is not a known PostgreSQL function... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/tab-to-sting-tp5812613p5813223.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.