uuid_xor.sql

--
-- Functions to generate an aggregate XOR of a UUID column [Postgres]
--
-- Useful to verify two tables are identical without computing
-- a full md5 checksum of entire table
--
-- Note, only uses the big-end 8 bytes of the UUID as there are
-- no 16-byte integers
--
-- Usage: select to_hex(aggrxoruuid(pk)) from mytable
-- where pk is of type UUID
--


create or replace function uuid2bit(val uuid) returns bigint
language sql
as
$$
select ('x' || translate(val::text, '-', ''))::bit(64)::bigint
$$;


create or replace function xoruuid(x1 bigint, u1 uuid) returns bigint
language sql
as
$$
select x1 # uuid2bit(u1)
$$;


create or replace aggregate aggrxoruuid(uuid)
(
	sfunc = xoruuid,
	stype = bigint,
	initcond = 0
);