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

Jon Lapham <lapham@extracta.com.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