Base58 Encoder function in PostgreSQL

Question

I found this MySQL function for Base58 Encoder in a Github Gist.

DELIMITER $$

CREATE FUNCTION base58_encode (num int) RETURNS varchar(255)
  DETERMINISTIC

BEGIN
  DECLARE alphabet varchar(255);
  DECLARE base_count int DEFAULT 0;
  DECLARE encoded varchar(255);
  DECLARE divisor DECIMAL(10,4);
  DECLARE mode int DEFAULT 0;

  SET alphabet = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  SET base_count = CHAR_LENGTH(alphabet);
  SET encoded = "";

  WHILE num  >= base_count DO
    SET divisor = num / base_count;
    SET mode = (num - (base_count* TRUNCATE(divisor,0)));
    SET encoded = CONCAT(SUBSTRING(alphabet FROM mode+1 FOR 1), encoded);
    SET num = TRUNCATE(divisor,0);
  END WHILE;

  SET encoded = CONCAT(SUBSTRING(alphabet FROM num+1 FOR 1), encoded);

  RETURN (encoded);

END

I am new to PostgreSQL and having difficulty converting above function to PostgreSQL function.

How would be the equivalent PostgreSQL function of above SQL snippet for Base58 Encoder?

What have you try, where you have problem? Because this should be solve easy reading the manual — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jan 13, 2016 at 2:28
Hey @JuanCarlosOropeza, thanks for reminding about the "Manual". Posted PostgreSQL version. stackoverflow.com/a/34759277/1433665. Thanks! — TheKojuEffect
– TheKojuEffect, Commented Jan 13, 2016 at 5:52

TheKojuEffect · Accepted Answer · 2016-01-13 05:50:04Z

5

Equivalent function in PostgreSQL that I came up with is as follows.

CREATE FUNCTION base58_encode(num INT)
  RETURNS VARCHAR(255) AS $encoded$

DECLARE
  alphabet   VARCHAR(255);
  base_count INT DEFAULT 0;
  encoded    VARCHAR(255);
  divisor    DECIMAL(10, 4);
  mod        INT DEFAULT 0;

BEGIN
  alphabet := '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  base_count := char_length(alphabet);
  encoded := '';

  WHILE num >= base_count LOOP
    divisor := num / base_count;
    mod := (num - (base_count * trunc(divisor, 0)));
    encoded := concat(substring(alphabet FROM mod + 1 FOR 1), encoded);
    num := trunc(divisor, 0);
  END LOOP;

  encoded = concat(substring(alphabet FROM num + 1 FOR 1), encoded);

  RETURN (encoded);

END; $encoded$
LANGUAGE PLPGSQL;

answered Jan 13, 2016 at 5:50

TheKojuEffect

21.2k20 gold badges95 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jan Drábek Over a year ago

There is a missing l inside the small letters!

TheKojuEffect Over a year ago

@JanDrábek l is left out so that it isn't confused with 1. I, O etc are also left out.

Jan Drábek Over a year ago

Oh I see... In that case I would suggest adding a comment as I spent an hour debugging why this code provides different output than my PHP code...

pba Over a year ago

I believe Jan Drábek made a confusion between base64 and base58. But even for people really looking at base58, be careful since the alphabet in above function is not the usual one associated with base58. In tools.ietf.org/id/draft-msporny-base58-01.html the alphabet is 123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz with capital letters first.

Mike Blackwell · Accepted Answer · 2021-03-01 16:17:58Z

3

For completeness here's a quick and dirty swipe at the inverse base58_decode() function:

CREATE OR REPLACE FUNCTION base58_decode(str VARCHAR(255))
  RETURNS BIGINT AS $$
DECLARE
  alphabet VARCHAR(255);
  c CHAR(1);
  p INT;
  v BIGINT;
BEGIN
  alphabet := '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  v := 0;
  FOR i IN 1..char_length(str) LOOP
    c := substring(str FROM i FOR 1);
    -- This is probably wildly inefficient, but we're just using this function for diagnostics...
    p := position(c IN alphabet);
    IF p = 0 THEN
      RAISE 'Illegal base58 character ''%'' in ''%''', c, str;
    END IF;
    v := (v * 58) + (p - 1);
  END LOOP;
  RETURN v;
END;$$
LANGUAGE PLPGSQL;

answered Mar 1, 2021 at 16:17

Mike Blackwell

4971 gold badge6 silver badges12 bronze badges

Comments

Mykhailo Tiutin · Accepted Answer · 2022-09-07 10:25:35Z

I've modified a sample for the Postgres 9.x above. It's proved to work with large numbers (blockchain addresses, for example)

CREATE OR REPLACE FUNCTION base58_encode (num numeric)
RETURNS text AS
$body$
declare
  --alphabet text = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  alphabet text[] = array[
    '1','2','3','4','5','6','7','8','9', 
    'A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z',
    'a','b','c','d','e','f','g','h','i','j','k','m','n','o','p','q','r','s','t','u','v','w','x','y','z'
   
  ];
  cnt integer = 58;
  dst text = '';
  _mod numeric;
begin
  while (num >= cnt) loop
    _mod = num % cnt;
    num = (num - _mod) / cnt;
    dst = alphabet[_mod+1] || dst;
  end loop;
  return alphabet[num+1] || dst;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
COST 100;

TheKojuEffect · Accepted Answer · 2023-05-15 19:39:43Z

Here is a function to convert a uuid to base58. Since postgres can't generally handle the BigInt conversion from UUID, you can use an extension to put the JS directly in postgres:

CREATE EXTENSION IF NOT EXISTS plv8;

CREATE OR REPLACE FUNCTION uuid_to_base58(uuid uuid)
  RETURNS text AS $$
    const base58Alphabet = '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';
    let bigintValue = BigInt('0x' + uuid.toString().replace(/-/g, ''));
    let base58 = '';
    while (bigintValue > BigInt(0)) {
        const remainder = bigintValue % BigInt(base58Alphabet.length);
        bigintValue /= BigInt(base58Alphabet.length);
        base58 = base58Alphabet.charAt(Number(remainder)) + base58;
    }
    return base58;
$$ LANGUAGE plv8 IMMUTABLE;

Good for short IDs where you could convert back and forth:

d0269693-c9e5-4a0d-998e-a2e1579accc3  --->  Sho37CJBR5FqrV4FXPWhGN

Дмитрий Игнатьев · Accepted Answer · 2018-04-05 11:57:29Z

0

Postgres 9.x

CREATE OR REPLACE FUNCTION base58_encode (num bigint)
RETURNS text AS
$body$
declare
  --alphabet text = '123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ';
  alphabet text[] = array[
    '1','2','3','4','5','6','7','8','9', 
    'a','b','c','d','e','f','g','h','i','j','k','m','n','o','p','q','r','s','t','u','v','w','x','y','z',
    'A','B','C','D','E','F','G','H','J','K','L','M','N','P','Q','R','S','T','U','V','W','X','Y','Z'
  ];
  cnt integer = 58;
  dst text = '';
  mod integer;
begin
  while (num >= cnt) loop
    num = num / cnt;
    mod = num % cnt + 1;
    dst = alphabet[mod] || dst;
  end loop;

  return alphabet[num] || dst;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

answered Apr 5, 2018 at 11:57

Дмитрий Игнатьев

92 bronze badges

2 Comments

Jan Drábek Over a year ago

The SECURITY INVOKER seems quite unreasonable here.

pba Over a year ago

There are 2 bugs in this function: 1. lines "num = num / cnt; mod = num % cnt + 1;" must be swapped. 2. "return alphabet[num] || dst;" must be changed to "return alphabet[num + 1] || dst;"

Krtko · Accepted Answer · 2021-05-14 19:57:18Z

To continue the completeness, if you need to decode to a UUID (Int16) I built on @mike-blackwell's answer to do that. Gist

I would love some feedback and improvements. I have to imagine theres a better way to go from a 128 bit numeric to a UUID

Code:

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;

Collectives™ on Stack Overflow

Base58 Encoder function in PostgreSQL

6 Answers 6

4 Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related