tab_to_sting

I don't know what collect actually does, but just guessing, I would say
that you're looking for string_agg()

depesz

On Wed, Jul 23, 2014 at 6:12 PM, Ramesh T <rameshparnanditech@gmail.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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,

On Thu, Jul 24, 2014 at 10:42 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general