Re: pgcryto strangeness...

Sean Chittenden wrote:

> 
> Just started using the pgcrypt 0.4.2 (very cool stuff) and am having
> some strange errors (not so cool).  Can someone make sense of the SQL
> below?  I'm not 100% sure what's going on or not going on...
> 
> 
> host=# SELECT DIGEST('asdf', 'md5') FROM users_shadow;
>                      digest                     
> ------------------------------------------------
>  \221.\310\003\262\316I\344\245A\006\215IZ\265p
> (1 row)
> 
> host=# SELECT DIGEST(password, 'md5') FROM users_shadow;
> ERROR:  Function 'digest(varchar, unknown)' does not exist
>         Unable to identify a function that satisfies the given argument types
>         You may need to add explicit typecasts
> host=# SELECT DIGEST(CAST(password AS bytea), CAST('md5' AS TEXT)) FROM users_shadow;
> ERROR:  Cannot cast type 'varchar' to 'bytea'
> 
> 
>     Any ideas as to how I can do this?  -sc
> 

You can't directly cast varchar to bytea, but you can use decode(in 7.2):

test=# select version();                           version
------------------------------------------------------------- PostgreSQL 7.2b3 on i686-pc-linux-gnu, compiled by GCC
2.96
(1 row)

test=# create table users_shadow(password varchar(20));
CREATE
test=# insert into users_shadow values('secret');
INSERT 1492547 1
test=# SELECT DIGEST(decode(password,'escape'), 'md5') FROM users_shadow;                        digest
------------------------------------------------------ ^\276"\224\354\320\340\360\216\253v\220\322\246\356i
(1 row)

HTH,


-- Joe