Hi,
Attached is a patch implementing a new aggregate function md5_agg() to
compute the aggregate MD5 sum across a number of rows. This is
something I've wished for a number of times. I think the primary use
case is to do a quick check that 2 tables, possibly on different
servers, contain the same data, using a query like
SELECT md5_agg(foo.*::text) FROM (SELECT * FROM foo ORDER BY id) foo;
or
SELECT md5_agg(foo.*::text ORDER BY id) FROM foo;
these would be equivalent to
SELECT md5(string_agg(foo.*::text, '' ORDER BY id)) FROM foo;
but without the excessive memory consumption for the intermediate
concatenated string, and the resulting 1GB table size limit.
I've added 2 variants: md5_agg(text) and md5_agg(bytea) to match the 2
variants of md5(), so pure binary data can also be checksummed.
In passing, I've tidied up and optimised the code in md5.c a bit ---
specifically I've removed the malloc()/memcpy()/free() code that was
unnecessarily making a copy of the entire input data just to pad it
and append the bit count. This reduces the memory consumption of the
existing md5() functions for large inputs, and gives a modest
performance boost. As a result, the md5() function can no longer throw
an out-of-memory error.
Regards,
Dean