Re: How to cast a char[] to varchar?

Jon Lapham <lapham(at)extracta(dot)com(dot)br> writes:
> I have a column defined as "char(3)[]" which I would like to copy into a
> different column defined as "varchar(255)".

> PS: It would be fine if the things in "cat2" contained the braces and
> quotes, it does not need to be cleaned-up.

You're going to need to do a little programming. plpgsql provides about
the simplest solution, as it will happily try to convert anything to
anything else (if it can out-convert the source value to text and then
in-convert to the destination type without error, it's happy). So:

regression=# create function to_varchar(char[]) returns varchar as '
regression'# begin
regression'# return $1;
regression'# end;' language 'plpgsql';
CREATE
regression=# create table foo (f1 char(3)[]);
CREATE
regression=# insert into foo values ('{"col","dep"}');
INSERT 299666 1
regression=# insert into foo values ('{"fee","fi", "fo","fum"}');
INSERT 299667 1
regression=# select f1, to_varchar(f1) from foo;
f1 | to_varchar
-----------------------+-----------------------
{col,dep} | {col,dep}
{fee,"fi ","fo ",fum} | {fee,"fi ","fo ",fum}
(2 rows)

If you wanted to be smarter --- like, say, getting rid of the braces and
so on --- you could code the conversion routine in pltcl or plperl,
either of which provide much better text-mashing capability than plpgsql
does. I seem to recall that pltcl supports Postgres arrays better than
either of the others do, so it might be the best bet for this
particular task.

regards, tom lane