PostgreSQL 8.2 -- How to get a string representation of any array? -

PostgreSQL 8.2 -- How to get a string representation of any array? -

in postgresql8.2, what's inverse of postgresql's text-to-array conversion?

as know:

select '{a,b,c}'::text[] x, '{{0,1},{2,3}}'::int[][] y; /* * x | y * ---------+--------------- * {a,b,c} | {{0,1},{2,3}} */

is there predefined function f(anyarray) or operator inverse? "inverse", mean applying f(x)::text[] or f(y)::int[][] bring array form.

the next hypothetical example, illustrate point.

select 'x='||f(x) x_str, 'y'=f(y) y_str ( select '{a,b,c}'::text[] x, '{{0,1},{2,3}}'::int[][] y; ) a; /* * x | y * -----------+----------------- * x={a,b,c} | y={{0,1},{2,3}} */

edit: unfortunately (and trust me, that's first thing had tried before wasting anyone's time here), select x::text doesn't work me (because i'm stuck postgresql8.2 --i utilize greenplum):

test=> select ('{a,b,c}'::text[])::text; error: cannot cast type text[] text line 1: select ('{a,b,c}'::text[])::text; ^

edit #2: unlike has been asserted some, has nil greenplum. has postgresql8.2. verified anyarray cannot cast text on vanilla postgresql 8.2.

in fact, alter makes select ('{a,b,c}'::text[])::text; work introduced in version 8.3.0, per next alter log (in history file distributed source):

* create general mechanism supports casts , standard string types (text, varchar, char) *every* datatype, invoking datatype's i/o functions (tom) previously, such casts available types had specialized function(s) purpose. these new casts assignment-only in to-string direction, explicit-only in other direction, , hence should create no surprising behavior.

in order me 100% this, went ahead , compiled source both pg 8.2.23 , 8.3.0. indeed, in 8.3.0 works:

test=# select version(); version ------------------------------------------------------------------------------------------------------------ postgresql 8.3.0 on x86_64-unknown-linux-gnu, compiled gcc gcc47 (gcc) 4.7.2 20121109 (red hat 4.7.2-8) (1 row) test=# select ('{a,b,c}'::text[])::text; text --------- {a,b,c} (1 row)

but not on 8.2.23:

test=# select version(); version ------------------------------------------------------------------------------------------------------------- postgresql 8.2.23 on x86_64-unknown-linux-gnu, compiled gcc gcc47 (gcc) 4.7.2 20121109 (red hat 4.7.2-8) (1 row) test=# select ('{a,b,c}'::text[])::text; error: cannot cast type text[] text line 1: select ('{a,b,c}'::text[])::text;

if don't understand question : next way text array , array text conversion

array_to_string(anyarray, text)

example: postgres=# select * array_to_string(array[1,2,3],''); array_to_string ----------------- 123 (1 row)

string_to_array(text, text) :

postgres=# select string_to_array('xx~^~yy~^~zz', '~^~'); string_to_array ----------------- {xx,yy,zz} (1 row)

if want read more array functions have look: http://www.postgresql.org/docs/8.2/static/functions-array.html

update #1: multi dimensional array:

create or replace function aaa(anyarray,text) returns setof text language plpgsql $function$ declare s $1%type; begin foreach s piece 1 in array $1 loop homecoming next array_to_string(s,$2); end loop; return; end; $function$; postgres=# select aaa('{{a,b,c},{x,y,z}}'::text[], ','); aaa ------- a,b,c x,y,z (2 rows) postgres=# select aaa('{a,b,c}'::text[], ','); aaa ------- a,b,c (1 row)

postgresql postgresql-8.2