带有 PL/pgSQL 的 CRC32 函数

如何计算 32 位循环冗余校验 (CRC-32) 作为 PostgreSQL 中的函数，方法与MySQL http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_crc32?

您可以自己创建该函数，这是 PostgreSQL 9.6 的工作示例

CREATE OR REPLACE FUNCTION crc32(text_string text) RETURNS bigint AS $$
DECLARE
    tmp bigint;
    i int;
    j int;
    byte_length int;
    binary_string bytea;
BEGIN
    IF text_string = '' THEN
        RETURN 0;
    END IF;

    i = 0;
    tmp = 4294967295;
    byte_length = bit_length(text_string) / 8;
    binary_string = decode(replace(text_string, E'\\\\', E'\\\\\\\\'), 'escape');
    LOOP
        tmp = (tmp # get_byte(binary_string, i))::bigint;
        i = i + 1;
        j = 0;
        LOOP
            tmp = ((tmp >> 1) # (3988292384 * (tmp & 1)))::bigint;
            j = j + 1;
            IF j >= 8 THEN
                EXIT;
            END IF;
        END LOOP;
        IF i >= byte_length THEN
            EXIT;
        END IF;
    END LOOP;
    RETURN (tmp # 4294967295);
END
$$ IMMUTABLE LANGUAGE plpgsql;

灵感来自于带有非 crc32 接受答案的旧帖子 https://stackoverflow.com/a/9653895/1455070。我找不到原始代码思考狮身人面像 https://github.com/freelancing-god/thinking-sphinx.