Date: Sun, 8 Apr 2007 11:37:57 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Dirk Jagdmann <jagdmann@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: MD5 sums of large objects
Message-ID: <20070408173757.GA78999@winnie.fuhr.org>
References: <5d0f60990704081003p615530acx86257a70bff33fa4@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <5d0f60990704081003p615530acx86257a70bff33fa4@mail.gmail.com>

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