Postgres PLPGSQL function that parses a UUID encoded with the Bitcoin base58 standard

base58_decode.sql

CREATE OR REPLACE FUNCTION base58_decode(encoded_id VARCHAR(22))
    RETURNS UUID AS $$
DECLARE
    -- Bitcoin base58 alphabet
    alphabet CHAR(58) := '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
    c CHAR(1) := null;
    p INT := null;
    raw_num NUMERIC := 0;
    uuid_str VARCHAR(32);
BEGIN
    /*
    Parses a UUID encoded with the Bitcoin base58 standard
    Use sparingly, any application connecting to the database should handle decoding the ID itself
    */

    -- Decode id to numeric
    FOR i IN 1..CHAR_LENGTH(encoded_id) LOOP
        c = SUBSTRING(encoded_id FROM i FOR 1);
        p = POSITION(c IN alphabet);
        raw_num = (raw_num * 58) + (p - 1);
    END LOOP;

    -- Parse NUMERIC into bytes
    -- There must be a better way to go from a NUMERIC -> UUID
    uuid_str := '';
    FOR i IN 0..31 LOOP
        uuid_str = CONCAT(uuid_str, TO_HEX(MOD(raw_num, 16)::INT));
        raw_num = DIV(raw_num, 16);
    END LOOP;

    return REVERSE(uuid_str)::UUID;
END;$$
LANGUAGE PLPGSQL;