postgresql PL/PGSQL functions for encoding 64-bit numbers as 8-byte strings and vice versa

pg_bytes.sql

-- transform 8 bytes into an int64
CREATE FUNCTION bytes_to_int64(s BYTEA, OUT result INT8)
AS $$
BEGIN
    SELECT bit_or(get_byte(s, ind)::INT8 << ((7 - ind) * 8))
           INTO result
           FROM generate_series(0, 7) AS ind;
END;
$$ LANGUAGE 'plpgsql';


-- transform an int64 into 8 bytes
CREATE FUNCTION int64_to_bytes(i INT8, OUT result BYTEA)
AS $$
BEGIN
    SELECT string_agg(set_byte(' ', 0, ((i >> boffset) & 255)::INTEGER), ''::BYTEA)
           INTO result
           FROM generate_series(56, 0, -8) AS boffset;
END;
$$ LANGUAGE 'plpgsql';