PostgreSQL: What is the best way to cast from from text to point[]? I'm looking for a way to cast from a text value:

'(180,90),(-180,-90)'


into a point[] value:

{"(180,90)","(-180,-90)"}

What is the best way to do this?

One possibility that I can think of is to manipulate the string to be of this form:

'{"(180, 90)", "(-180,-90)"}'


Which I can then cast to point[] like so:

select '{"(180, 90)", "(-180,-90)"}'::point[];


Surely there's a better way? The solution I currently have is this:

select concat('{', replace(replace(&lt;text&gt;, '(', '"('), ')', ')"'), '}')::point[]
Not any better than what you already got, but:

select string_to_array(replace('(180,90),(-180,-90)','),(',');('), ';')::point[];


If the separator token in the array would have been something else (say ";"), it would have been sufficient with:

select string_to_array('(180,90);(-180,-90)', ';')::point[];


EDIT:

If I get it right you have a foreign table like:

create table mytbl (p point[]);
insert into mytbl (p) 
values ( '{"(180, 90)", "(-180,-90)"}'::point[] );


which you access through a dblink. Would the following work ( I cant use DBLink at the moment )?

select string_to_array(array_to_string(p,';'),';') from mytbl;