CRC32C calculator for Postgresql in PL/PGSQL

crc32c.sql

-- crc32c https://tools.ietf.org/html/rfc3385#section-4.1
-- Table-based crc32 (not exactly right algorithm, but a good pattern to follow)
-- https://gist.github.com/cuber/bcf0a3a96fc9a790d96d
-- Google-created CRC table
-- https://github.com/googlearchive/crc32c-java/blob/master/src/com/google/cloud/Crc32c.java
CREATE OR REPLACE FUNCTION update_crc32c(src TEXT,
                                         crc BIGINT DEFAULT 0)
RETURNS BIGINT
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$$
DECLARE
    bytes   BYTEA;
    byte    BIGINT;
    len     INT;
    index   INT;

    long_mask   CONSTANT BIGINT = x'ffffffff'::BIGINT;
    crc_table   CONSTANT BIGINT[] = ARRAY[
        x'00000000'::BIGINT, x'f26b8303'::BIGINT, x'e13b70f7'::BIGINT, x'1350f3f4'::BIGINT,
        x'c79a971f'::BIGINT, x'35f1141c'::BIGINT, x'26a1e7e8'::BIGINT, x'd4ca64eb'::BIGINT,
        x'8ad958cf'::BIGINT, x'78b2dbcc'::BIGINT, x'6be22838'::BIGINT, x'9989ab3b'::BIGINT,
        x'4d43cfd0'::BIGINT, x'bf284cd3'::BIGINT, x'ac78bf27'::BIGINT, x'5e133c24'::BIGINT,
        x'105ec76f'::BIGINT, x'e235446c'::BIGINT, x'f165b798'::BIGINT, x'030e349b'::BIGINT,
        x'd7c45070'::BIGINT, x'25afd373'::BIGINT, x'36ff2087'::BIGINT, x'c494a384'::BIGINT,
        x'9a879fa0'::BIGINT, x'68ec1ca3'::BIGINT, x'7bbcef57'::BIGINT, x'89d76c54'::BIGINT,
        x'5d1d08bf'::BIGINT, x'af768bbc'::BIGINT, x'bc267848'::BIGINT, x'4e4dfb4b'::BIGINT,
        x'20bd8ede'::BIGINT, x'd2d60ddd'::BIGINT, x'c186fe29'::BIGINT, x'33ed7d2a'::BIGINT,
        x'e72719c1'::BIGINT, x'154c9ac2'::BIGINT, x'061c6936'::BIGINT, x'f477ea35'::BIGINT,
        x'aa64d611'::BIGINT, x'580f5512'::BIGINT, x'4b5fa6e6'::BIGINT, x'b93425e5'::BIGINT,
        x'6dfe410e'::BIGINT, x'9f95c20d'::BIGINT, x'8cc531f9'::BIGINT, x'7eaeb2fa'::BIGINT,
        x'30e349b1'::BIGINT, x'c288cab2'::BIGINT, x'd1d83946'::BIGINT, x'23b3ba45'::BIGINT,
        x'f779deae'::BIGINT, x'05125dad'::BIGINT, x'1642ae59'::BIGINT, x'e4292d5a'::BIGINT,
        x'ba3a117e'::BIGINT, x'4851927d'::BIGINT, x'5b016189'::BIGINT, x'a96ae28a'::BIGINT,
        x'7da08661'::BIGINT, x'8fcb0562'::BIGINT, x'9c9bf696'::BIGINT, x'6ef07595'::BIGINT,
        x'417b1dbc'::BIGINT, x'b3109ebf'::BIGINT, x'a0406d4b'::BIGINT, x'522bee48'::BIGINT,
        x'86e18aa3'::BIGINT, x'748a09a0'::BIGINT, x'67dafa54'::BIGINT, x'95b17957'::BIGINT,
        x'cba24573'::BIGINT, x'39c9c670'::BIGINT, x'2a993584'::BIGINT, x'd8f2b687'::BIGINT,
        x'0c38d26c'::BIGINT, x'fe53516f'::BIGINT, x'ed03a29b'::BIGINT, x'1f682198'::BIGINT,
        x'5125dad3'::BIGINT, x'a34e59d0'::BIGINT, x'b01eaa24'::BIGINT, x'42752927'::BIGINT,
        x'96bf4dcc'::BIGINT, x'64d4cecf'::BIGINT, x'77843d3b'::BIGINT, x'85efbe38'::BIGINT,
        x'dbfc821c'::BIGINT, x'2997011f'::BIGINT, x'3ac7f2eb'::BIGINT, x'c8ac71e8'::BIGINT,
        x'1c661503'::BIGINT, x'ee0d9600'::BIGINT, x'fd5d65f4'::BIGINT, x'0f36e6f7'::BIGINT,
        x'61c69362'::BIGINT, x'93ad1061'::BIGINT, x'80fde395'::BIGINT, x'72966096'::BIGINT,
        x'a65c047d'::BIGINT, x'5437877e'::BIGINT, x'4767748a'::BIGINT, x'b50cf789'::BIGINT,
        x'eb1fcbad'::BIGINT, x'197448ae'::BIGINT, x'0a24bb5a'::BIGINT, x'f84f3859'::BIGINT,
        x'2c855cb2'::BIGINT, x'deeedfb1'::BIGINT, x'cdbe2c45'::BIGINT, x'3fd5af46'::BIGINT,
        x'7198540d'::BIGINT, x'83f3d70e'::BIGINT, x'90a324fa'::BIGINT, x'62c8a7f9'::BIGINT,
        x'b602c312'::BIGINT, x'44694011'::BIGINT, x'5739b3e5'::BIGINT, x'a55230e6'::BIGINT,
        x'fb410cc2'::BIGINT, x'092a8fc1'::BIGINT, x'1a7a7c35'::BIGINT, x'e811ff36'::BIGINT,
        x'3cdb9bdd'::BIGINT, x'ceb018de'::BIGINT, x'dde0eb2a'::BIGINT, x'2f8b6829'::BIGINT,
        x'82f63b78'::BIGINT, x'709db87b'::BIGINT, x'63cd4b8f'::BIGINT, x'91a6c88c'::BIGINT,
        x'456cac67'::BIGINT, x'b7072f64'::BIGINT, x'a457dc90'::BIGINT, x'563c5f93'::BIGINT,
        x'082f63b7'::BIGINT, x'fa44e0b4'::BIGINT, x'e9141340'::BIGINT, x'1b7f9043'::BIGINT,
        x'cfb5f4a8'::BIGINT, x'3dde77ab'::BIGINT, x'2e8e845f'::BIGINT, x'dce5075c'::BIGINT,
        x'92a8fc17'::BIGINT, x'60c37f14'::BIGINT, x'73938ce0'::BIGINT, x'81f80fe3'::BIGINT,
        x'55326b08'::BIGINT, x'a759e80b'::BIGINT, x'b4091bff'::BIGINT, x'466298fc'::BIGINT,
        x'1871a4d8'::BIGINT, x'ea1a27db'::BIGINT, x'f94ad42f'::BIGINT, x'0b21572c'::BIGINT,
        x'dfeb33c7'::BIGINT, x'2d80b0c4'::BIGINT, x'3ed04330'::BIGINT, x'ccbbc033'::BIGINT,
        x'a24bb5a6'::BIGINT, x'502036a5'::BIGINT, x'4370c551'::BIGINT, x'b11b4652'::BIGINT,
        x'65d122b9'::BIGINT, x'97baa1ba'::BIGINT, x'84ea524e'::BIGINT, x'7681d14d'::BIGINT,
        x'2892ed69'::BIGINT, x'daf96e6a'::BIGINT, x'c9a99d9e'::BIGINT, x'3bc21e9d'::BIGINT,
        x'ef087a76'::BIGINT, x'1d63f975'::BIGINT, x'0e330a81'::BIGINT, x'fc588982'::BIGINT,
        x'b21572c9'::BIGINT, x'407ef1ca'::BIGINT, x'532e023e'::BIGINT, x'a145813d'::BIGINT,
        x'758fe5d6'::BIGINT, x'87e466d5'::BIGINT, x'94b49521'::BIGINT, x'66df1622'::BIGINT,
        x'38cc2a06'::BIGINT, x'caa7a905'::BIGINT, x'd9f75af1'::BIGINT, x'2b9cd9f2'::BIGINT,
        x'ff56bd19'::BIGINT, x'0d3d3e1a'::BIGINT, x'1e6dcdee'::BIGINT, x'ec064eed'::BIGINT,
        x'c38d26c4'::BIGINT, x'31e6a5c7'::BIGINT, x'22b65633'::BIGINT, x'd0ddd530'::BIGINT,
        x'0417b1db'::BIGINT, x'f67c32d8'::BIGINT, x'e52cc12c'::BIGINT, x'1747422f'::BIGINT,
        x'49547e0b'::BIGINT, x'bb3ffd08'::BIGINT, x'a86f0efc'::BIGINT, x'5a048dff'::BIGINT,
        x'8ecee914'::BIGINT, x'7ca56a17'::BIGINT, x'6ff599e3'::BIGINT, x'9d9e1ae0'::BIGINT,
        x'd3d3e1ab'::BIGINT, x'21b862a8'::BIGINT, x'32e8915c'::BIGINT, x'c083125f'::BIGINT,
        x'144976b4'::BIGINT, x'e622f5b7'::BIGINT, x'f5720643'::BIGINT, x'07198540'::BIGINT,
        x'590ab964'::BIGINT, x'ab613a67'::BIGINT, x'b831c993'::BIGINT, x'4a5a4a90'::BIGINT,
        x'9e902e7b'::BIGINT, x'6cfbad78'::BIGINT, x'7fab5e8c'::BIGINT, x'8dc0dd8f'::BIGINT,
        x'e330a81a'::BIGINT, x'115b2b19'::BIGINT, x'020bd8ed'::BIGINT, x'f0605bee'::BIGINT,
        x'24aa3f05'::BIGINT, x'd6c1bc06'::BIGINT, x'c5914ff2'::BIGINT, x'37faccf1'::BIGINT,
        x'69e9f0d5'::BIGINT, x'9b8273d6'::BIGINT, x'88d28022'::BIGINT, x'7ab90321'::BIGINT,
        x'ae7367ca'::BIGINT, x'5c18e4c9'::BIGINT, x'4f48173d'::BIGINT, x'bd23943e'::BIGINT,
        x'f36e6f75'::BIGINT, x'0105ec76'::BIGINT, x'12551f82'::BIGINT, x'e03e9c81'::BIGINT,
        x'34f4f86a'::BIGINT, x'c69f7b69'::BIGINT, x'd5cf889d'::BIGINT, x'27a40b9e'::BIGINT,
        x'79b737ba'::BIGINT, x'8bdcb4b9'::BIGINT, x'988c474d'::BIGINT, x'6ae7c44e'::BIGINT,
        x'be2da0a5'::BIGINT, x'4c4623a6'::BIGINT, x'5f16d052'::BIGINT, x'ad7d5351'::BIGINT
        ];
BEGIN
    crc = crc # long_mask;
    bytes = decode(src, 'escape');
    len = length(bytes);
    FOR i IN 0..len - 1 LOOP
        byte = get_byte(bytes, i);
        index = (crc # byte) & x'ff'::BIGINT;
        crc = crc_table[index + 1] # (crc >> 8);
    END LOOP;
    RETURN crc # long_mask;
END
$$;