Thread: md5 function

Hello,

Sorry for just stupid question, but I need use md5 function in 7.4
When I write:
select md5('text');
ERROR:  Function md5("unknown") does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts

where is problem???

thanx, miso

On Wed, Dec 17, 2003 at 09:47:01AM +0100, Miso Hlavac wrote:
> Sorry for just stupid question, but I need use md5 function in 7.4
> When I write:
> select md5('text');
> ERROR:  Function md5("unknown") does not exist
>         Unable to identify a function that satisfies the given argument types
>         You may need to add explicit typecasts
>
> where is problem???

Are you sure the server is 7.4?  What does SELECT VERSION() show?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Wednesday 17 December 2003 08:47, Miso Hlavac wrote:
> Hello,
>
> Sorry for just stupid question, but I need use md5 function in 7.4
> When I write:
> select md5('text');
> ERROR:  Function md5("unknown") does not exist
>         Unable to identify a function that satisfies the given argument
> types You may need to add explicit typecasts

Have a look in the contrib/crypto add-on, I think md5() is in there.

--
  Richard Huxton
  Archonet Ltd

On Wed, Dec 17, 2003 at 09:37:07AM +0000, Richard Huxton wrote:
> On Wednesday 17 December 2003 08:47, Miso Hlavac wrote:
> > Hello,
> >
> > Sorry for just stupid question, but I need use md5 function in 7.4
> > When I write:
> > select md5('text');
> > ERROR:  Function md5("unknown") does not exist
> >         Unable to identify a function that satisfies the given argument
> > types You may need to add explicit typecasts
>
> Have a look in the contrib/crypto add-on, I think md5() is in there.

md5() should be stock in 7.4.

mydb=> \x
Expanded display is on.
mydb=> \df+ md5
List of functions
-[ RECORD 1 ]-------+--------------------
Result data type    | text
Schema              | pg_catalog
Name                | md5
Argument data types | text
Owner               | pgsql
Language            | internal
Source code         | md5_text
Description         | calculates md5 hash

The internal function md5_text() is in src/backend/utils/adt/varlena.c.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Miso Hlavac wrote:

> Hello,
>
> Sorry for just stupid question, but I need use md5 function in 7.4
> When I write:
> select md5('text');
> ERROR:  Function md5("unknown") does not exist
>         Unable to identify a function that satisfies the given argument types
>         You may need to add explicit typecasts
>
> where is problem???

I'm using 7.4 and it's working.
$ psql
Welcome to psql 7.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

db=# select md5('test');
                md5
----------------------------------
  098f6bcd4621d373cade4e832627b4f6
(1 row)

On Wed, 17 Dec 2003, Michael Fuhr wrote:

> md5() should be stock in 7.4.

Is there a way to, when I add a record to a table, have the md5 hash
computed and stored in the same table and then returned to the calling
program?  Currently, I'm using the perl md5 function to compute the hash
and store it in the DB but I'm thinking that offloading this to the DB
itself might be faster (of course, if that's not true and the way I'm
doing things now is fine, then I'll leave it as it is).

--
Jon Earle

SAVE FARSCAPE http://www.savefarscape.com/

Vegetarian - an old Indian word meaning 'lousy hunter'.

Jon Earle <je_pgsql@kronos.honk.org> writes:

> Is there a way to, when I add a record to a table, have the md5 hash
> computed and stored in the same table and then returned to the calling
> program?  Currently, I'm using the perl md5 function to compute the hash
> and store it in the DB but I'm thinking that offloading this to the DB
> itself might be faster (of course, if that's not true and the way I'm
> doing things now is fine, then I'll leave it as it is).

If your webserver is heavily loaded and the DB server isn't too busy,
this might be a win; otherwise it's unlikely to make any difference.
I think perl calls out to C to do the md5 computation, so it's just as
fast as the version in Postgres.

That said, the way to do it if you wanted to would be to write an
insert_my_record() function that stores the data for the record and
returns the md5 hash.

-Doug

On Wed, 2003-12-17 at 06:30, Jon Earle wrote:

Is there a way to, when I add a record to a table, have the md5 hash
computed and stored in the same table and then returned to the calling
program?

I recommend that you write a trigger to compute the md5 and shove that into a column. There are several integrity advantages of having postgresql do it in a trigger: 1) you can guarantee that it gets done, 2) you don't have to worry about a clients lying about the md5 or computing the md5 in different ways, 3) the md5 gets computed regardless of how the record is inserted, and 4) you can attach the trigger to updates as well.

As for returning the md5 to the caller/client, that's probably best done with a function whose job is to insert a record and return the md5. I have functions like serial# <- ins_record(datum,datum,datum), which merely do an insert into table and return the serial number for that record.

-Reece

-- 
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0