Thread: MD5 sums of large objects
Hello all together, 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... Now I don't know if something like this is already included in the PostgreSQL distribution, could be found somewhere on pgfoundry or thirdly how to do it? If I have to program myself I would go for a C-language function which would use the libpq large-object functions to create the hashsums. -- ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org
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
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 fromlibpq-fs.h SEEK_SET constant integer := 0; SEEK_END constant integer := 2;BEGIN IF id is null THEN RETURN NULL; ENDIF; 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
On Mon, Apr 09, 2007 at 02:07:16AM +0200, Dirk Jagdmann wrote: > this works like charm. Although I did fix the argument for lo_lseek: Oops; thanks for fixing that. > INV_READ constant integer := 262144; -- 0x40000 from libpq-fs.h You could also use a hex constant (the cast to integer is necessary): INV_READ constant integer := x'40000'::integer; > IF id is null THEN > RETURN NULL; > END IF; The above check is unnecessary since the function is marked STRICT, aka RETURNS NULL ON NULL INPUT. > 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. You could make a proposal in pgsql-hackers but I think 8.3 is in feature freeze so don't expect to see it until 8.4, if it's accepted at all. There's always PgFoundry :-) -- Michael Fuhr
Hello Michael, thanks for the comments on my corrected function. > You could make a proposal in pgsql-hackers but I think 8.3 is in > feature freeze so don't expect to see it until 8.4, if it's accepted > at all. There's always PgFoundry :-) I'll now see how this performs in my application and if I'm satisfied with this solution I'll post something on pgsql-hackers of pgfoundry. -- ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org