Re: MD5 sums of large objects

On Sun, Apr 08, 2007 at 07:03:17PM +0200, Dirk Jagdmann wrote:
> I have a database containing lots of large objects. Now I'd like to
> compare large objects in my database and I thought of having a
> function which creates a hashsum (MD5, SHA-1 or whatever) of my large
> object, so I can use that in queries:
>
> create function lo_md5(id oid) returns text...

Something like this might work:

create function lo_md5(id oid) returns text as $$
declare
fd integer;
size integer;
hashval text;
INV_READ constant integer := 262144;
SEEK_SET constant integer := 0;
SEEK_END constant integer := 2;
begin
fd := lo_open(id, INV_READ);
size := lo_lseek(0, 0, SEEK_END);
perform lo_lseek(0, 0, SEEK_SET);
hashval:= md5(loread(fd, size));
perform lo_close(fd);
return hashval;
end;
$$ language plpgsql stable strict;

For hash functions other than MD5 see contrib/pgcrypto.

--
Michael Fuhr