two PL/pgSQL functions for PostgreSQL. I wanted to use those functions for serial number on X.509 certificates, but I noticed serial number may be over the capacity of bigint, then gave up and stored serial number as hexadecimal format on varchar.

postgresql_function_for_serialnumber.sql

-- from decimal biginteger to hexdecimal text with any separator
CREATE OR REPLACE FUNCTION serialnumber_hex(serial bigint, sep varchar) RETURNS varchar AS $$
DECLARE
    org varchar := '';
    res varchar := '';
BEGIN
    org := to_hex(serial);
    WHILE 0 < length(org) LOOP
        res := concat_ws(sep, res, substr(org, 1, 2));
        org := substr(org, 3, length(org));
    END LOOP;
    RETURN substr(res, length(sep) + 1, length(res));
END;
$$ LANGUAGE plpgsql;

-- from hexdecimal text with ':' or ' ' as a separator to decimal biginteger
CREATE OR REPLACE FUNCTION serialnumber_dec(serial varchar) RETURNS varchar AS $$
DECLARE
    res bigint;
    serial_hex varchar;
BEGIN
    serial_hex := replace(replace(serial, ':', ''), ' ', '');
    EXECUTE 'SELECT x''' || serial_hex || '''::bigint' INTO res;
    RETURN res;
END;
$$ LANGUAGE plpgsql;