srfrog · November 27, 2019 03:49 · Nov 27, 2019
diff --git a/cuid.sql b/cuid.sql
@@ -0,0 +1,134 @@
+-- Collision-resistant ids optimized for horizontal scaling and performance, for PL/PgSQL.
+-- Based on https://github.com/ericelliott/cuid
+-- Version 1.0.0
+
+-- Usage: SELECT cuid();
+
+-- BEGIN CONFIG ---
+
+-- Put a unique host ID (int) here per server instance.
+-- Once set, this value should not be changed.
+SET plpgsql.cuid_hostid = "897892";
+
+-- Create the sequence used to track ID counts. Used in _safeCounter()
+CREATE SEQUENCE IF NOT EXISTS "cuid_seq";
+
+-- END OF CONFIG ---
+
+-- Get the next ID in counter. The sequence will reset after max_id is reached.
+CREATE OR REPLACE FUNCTION _safeCounter() RETURNS VARCHAR AS $$
+DECLARE
+  seq_id bigint;
+  max_id int := 1679616;
+BEGIN
+  SELECT nextval('cuid_seq') INTO seq_id;
+
+  IF seq_id > max_id THEN
+    SELECT setval('cuid_seq', 0) INTO seq_id;
+  END IF;
+
+  RETURN pad(base36enc(seq_id), 4);
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Uses the backend PID and a unique host ID to generate a fingerprint.
+-- Unfortunately getting the hostname of a Pg server is rather expensive so we opt for
+-- a static unique host ID.
+CREATE OR REPLACE FUNCTION _fingerprint() RETURNS VARCHAR AS $$
+DECLARE
+  padding int := 2;
+  ret varchar;
+BEGIN
+  ret := pad(base36enc(pg_backend_pid()), padding);
+  ret := ret || pad(base36enc(current_setting('plpgsql.cuid_hostid')::bigint), padding);
+  RETURN ret;
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Encodes a base-10 bigint to a base-36 string representation.
+CREATE OR REPLACE FUNCTION base36enc(num bigint) RETURNS VARCHAR AS $$
+DECLARE
+  base36 char[];
+  ret varchar;
+  val bigint;
+  tmp bigint;
+BEGIN
+  base36 := ARRAY[
+    '0','1','2','3','4','5','6','7','8','9','a','b',
+    'c','d','e','f','g','h','i','j','k','l','m','n',
+    'o','p','q','r','s','t','u','v','w','x','y','z'
+  ];
+
+  IF num ISNULL THEN
+    RETURN NULL;
+  END IF;
+
+  val := num;
+  ret := '';
+  LOOP
+      tmp := val % 36 + 1;
+      ret := base36[tmp] || ret;
+      val := val / 36;
+      IF val = 0 THEN
+          exit;
+      END IF;
+  END LOOP;
+
+  IF num < 0 THEN
+      ret := '-' || ret;
+  END IF;
+
+  RETURN ret;
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Adds zero-padding of a size to a num string. If the string is longer than size
+-- it gets truncated to the left.
+CREATE OR REPLACE FUNCTION pad(num varchar, size int) RETURNS VARCHAR AS $$
+BEGIN
+  RETURN lpad(right(num, size), size, '0');
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Get value of current timestamp encoded as base-36
+CREATE OR REPLACE FUNCTION _timestamp() RETURNS VARCHAR AS $$
+DECLARE
+  now_ts bigint;
+BEGIN
+  SELECT floor(extract(EPOCH FROM clock_timestamp()))
+    INTO now_ts;
+
+  RETURN base36enc(now_ts);
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Get a random base-36 valid string of size block_size.
+CREATE OR REPLACE FUNCTION _randomBlock() RETURNS VARCHAR AS $$
+DECLARE
+  base36 varchar := '0123456789abcdefghijklmnopqrstuvwxyz';
+  block_size int := 4;
+  val varchar;
+BEGIN
+  SELECT array_to_string(ARRAY(
+    SELECT substring(base36 FROM (random() * 36)::int FOR 1)
+      FROM generate_series(1, block_size)), '', '0')
+    INTO val;
+
+  RETURN val;
+END;
+$$ LANGUAGE PLPGSQL;
+
+-- Generates a new Collision-resistant ID (CUID)
+CREATE OR REPLACE FUNCTION cuid() RETURNS VARCHAR AS $$
+DECLARE
+  ret varchar;
+BEGIN
+  ret := 'c';
+  ret := ret || _timestamp();
+  ret := ret || _safeCounter();
+  ret := ret || _fingerprint();
+  ret := ret || _randomBlock();
+  ret := ret || _randomBlock();
+  RETURN ret;
+END;
+$$ LANGUAGE PLPGSQL;