Обсуждение: How to cast a char[] to varchar?

I have a column defined as "char(3)[]" which I would like to copy into a
different column defined as "varchar(255)".

I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
I get an error message, "Cannot cast type '_bpchar' to 'varchar'".

Any suggestions?

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

On Mon, 7 Jan 2002, Jon Lapham wrote:

> I have a column defined as "char(3)[]" which I would like to copy into a
> different column defined as "varchar(255)".
>
> I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
> I get an error message, "Cannot cast type '_bpchar' to 'varchar'".
>
> Any suggestions?

What are you trying to get out?  You've got a char(3) array, what do you
want the varchar to have in the end?

Stephan Szabo wrote:

> On Mon, 7 Jan 2002, Jon Lapham wrote:
>
>
>>I have a column defined as "char(3)[]" which I would like to copy into a
>>different column defined as "varchar(255)".
>>
>>I've tried to cast the char(3)[] field using "CAST( ... as varchar)" but
>>I get an error message, "Cannot cast type '_bpchar' to 'varchar'".
>>
>>Any suggestions?
>>
>
> What are you trying to get out?  You've got a char(3) array, what do you
> want the varchar to have in the end?
>
>


Assuming "category" is the char(3)[] column, and "cat2" is the desired
varchar(255) column, I would like the following:


main_v0_8=# select peopleid, category, cat2 from people where
peopleid=100010;
  peopleid |   category    |  cat2
----------+---------------+---------
    100010 | {"col","dep"} | col dep
(1 row)

However, in the real database the char(3) array "category" may contain
anywhere from 0 to 10 items.

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

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

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

Tom Lane wrote:

> regression=# create function to_varchar(char[]) returns varchar as '
> regression'# begin
> regression'#   return $1;
> regression'# end;' language 'plpgsql';

Thanks Tom, works great.

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham
  Extracta Moléculas Naturais, Rio de Janeiro, Brasil
  email: lapham@extracta.com.br      web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------