Thread: Problem with encode() function

I have a table where I have stored plain text passwords in a varchar field.
I would like to convert these to an encoded field. I have been able to
insert values into a test table using this syntax with a literal password:

insert into testtable (name, password) values ('glenn',encode
('mypassword','base64'));

and I can read them back out with the decode(password,'base64') syntax.
This is the behavior I want.

But when I try to encode data from an existing table:

create table newtable as select name, encode(password,'base64') as password
from oldtable;

I get this error

ERROR:  Function encode(character varying, "unknown") does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts


I have tried the following casts

password::text
case(password as text)

but I still get the error:

ERROR:  Function encode(text, "unknown") does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts

Is there a way I can accomplish this task?

Thanks.

Glenn_Wiens@via-christi.org writes:

> but I still get the error:
>
> ERROR:  Function encode(text, "unknown") does not exist
>         Unable to identify a function that satisfies the given argument
> types
>         You may need to add explicit typecasts
>
> Is there a way I can accomplish this task?

The problem is that there is no cast from varchar to bytea (which is
what encode takes as first argument). Try creating the cast first:

  CREATE CAST (varchar as bytea) WITHOUT FUNCTION;
  SELECT encode(password::bytea, 'base64'::text) FROM texttable;

You may want to drop the cast afterwards.

Regards,
Manuel.

Thanks -- that did the trick! And when I went to consult my documentation
about this command, I realized I was looking at 7.2 docs (downloaded) vs my
currently running version of 7.3.





                    Manuel Sugawara
                    <masm@fciencias.una       To:     Glenn_Wiens@via-christi.org
                    m.mx>                     cc:     pgsql-novice@postgresql.org
                    Sent by:                  Fax to:
                    masm@conexa.fcienci       Subject:     Re: [NOVICE] Problem with encode() function
                    as.unam.mx


                    05/21/2004 05:21 PM






Glenn_Wiens@via-christi.org writes:

> but I still get the error:
>
> ERROR:  Function encode(text, "unknown") does not exist
>         Unable to identify a function that satisfies the given argument
> types
>         You may need to add explicit typecasts
>
> Is there a way I can accomplish this task?

The problem is that there is no cast from varchar to bytea (which is
what encode takes as first argument). Try creating the cast first:

  CREATE CAST (varchar as bytea) WITHOUT FUNCTION;
  SELECT encode(password::bytea, 'base64'::text) FROM texttable;

You may want to drop the cast afterwards.

Regards,
Manuel.