DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=AzcllJ6ehywYDptScPTJVpe0PK9yfR3q6+QI5oSfUHYIIl7HTHruQmPRhcKh8QKr+fD9SttTX/YTIKzu4iG2CtdhEkjPJw3d+LXPBgtXk1REk24AGUZoxhOl/9IiqvtBiP6ISeM2ie5zpGruiB+FDKcRBqAbhjV6t9FmayoDJAY=
Message-ID: <5d0f60990704081707m56b29423ob5d63103623305b2@mail.gmail.com>
Date: Mon, 9 Apr 2007 02:07:16 +0200
From: "Dirk Jagdmann" <jagdmann@gmail.com>
To: "Michael Fuhr" <mike@fuhr.org>
Subject: Re: MD5 sums of large objects
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20070408173757.GA78999@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <5d0f60990704081003p615530acx86257a70bff33fa4@mail.gmail.com>
	<20070408173757.GA78999@winnie.fuhr.org>

Hello Michael,

this works like charm. Although I did fix the argument for lo_lseek:

CREATE OR REPLACE FUNCTION md5(id oid)
RETURNS text
as $$
 DECLARE
  fd        integer;
  size      integer;
  hashval   text;
  INV_READ  constant integer := 262144; -- 0x40000 from libpq-fs.h
  SEEK_SET  constant integer := 0;
  SEEK_END  constant integer := 2;
 BEGIN
  IF id is null THEN
    RETURN NULL;
  END IF;
  fd   := lo_open(id, INV_READ);
  size := lo_lseek(fd, 0, SEEK_END);
  PERFORM lo_lseek(fd, 0, SEEK_SET);
  hashval := md5(loread(fd, size));
  PERFORM lo_close(fd);
  RETURN hashval;
 END;
$$
language plpgsql stable strict;
comment on FUNCTION md5(id oid) is 'Calculates the md5 sum of a large object.';

I vote for this function beeing included either somewhere in the
contrib directories, as you often don't need the full power of
pgcrypto is md5 suffices for your hashing needs.

-- 
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org