Postgres: How to convert 16 bytes into 4 bytes by XOR-ing every 4 bytes

Question

I have a 16 byte md5 hash that I need to "fold" into 4-byte data using XOR: {1st 4 bytes} XOR {2nd 4 bytes} XOR {3rd 4 bytes} XOR {4th 4 bytes}. I then need to convert the result to Hex form (8 char string).

I'm generating my hash like this (decoding to hex as it seems like it'll be easier to deal with):

SELECT decode(md5('test'), 'hex');

But that's as far as I got. I don't know the best way split the 16 byte hash into 4, 4-byte values, then XOR those 4-byte values.

Answer 1

After spending some time understanding this answer, I was able to come up with this:

CREATE OR REPLACE FUNCTION compressed_md5(var_txt TEXT) RETURNS TEXT
AS $$
DECLARE
  var_hash BYTEA;
  var_compressedHash BYTEA;
  var_offset INTEGER;
BEGIN
  var_hash := decode(md5(var_txt), 'hex');
  var_compressedHash := decode('00000000', 'hex'); -- prepopulate with some 4-byte data
  FOR var_offset IN 0..3 LOOP
    var_compressedHash := set_byte(var_compressedHash, var_offset,
                                   get_byte(var_hash, var_offset) #
                                   get_byte(var_hash, var_offset + 4) #
                                   get_byte(var_hash, var_offset + 8) #
                                   get_byte(var_hash, var_offset + 12));
  END LOOP;
  RETURN encode(var_compressedHash, 'hex');
END;
$$ LANGUAGE plpgsql;

SELECT compressed_md5('test');

Result:

"a35742cb"