thesmart · May 5, 2020 17:25
diff --git a/hid_generate.sql b/hid_generate.sql
 -- This function generates ordered, base36 alpha-numeric ids similar to Slack's ID scheme.
 --
 -- I wanted a primary key scheme that had the following features:
 --   1) Lexical order, so that `ORDER BY` works as expected.
 --   2) Prevents sampling an auto-incrementing primary key to determine growth over time.
 --   3) Shorter and more human-friendly than BIGINT and UUID keys.
 --   4) Has a prefix such that table can be inferred from any record's primary or foreign key.
 --
 -- It is suitable for use as primary key, provided a few assumptions are true:
 --   1) You do not attempt to genereate more than 10M hids per second (system-time).
 --   2) If system clock is reset backwards, do not violate assumption #1.
 --   3) System clock is never set to a time prior to Jan 1, 1970.
 --
 -- To use, define your table like:
 -- ```
 -- CREATE SEQUENCE examples_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
 -- CREATE TABLE examples (
 --  id character varying DEFAULT hid_generate('E', 'examples') NOT NULL
 -- )
 -- ```

 CREATE FUNCTION hid_generate(prefix character varying, tbl_name character varying) RETURNS character varying LANGUAGE plpgsql AS $$
 DECLARE
  alphabet constant text[] := string_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'::character varying, null);
  hid character varying := '';
  seq_id bigint;
  unix_ms bigint;
  remainder bigint;
 BEGIN
  -- low characters are sequence based so we can generate up to 10,000 unique hids per millisecond 
  SELECT nextval(tbl_name || '_id_seq') % 10000 INTO seq_id; 
  LOOP
    remainder := seq_id % 36::bigint;
    seq_id := seq_id / 36::bigint;
    hid := '' || alphabet[(remainder + 1)] || hid;
  EXIT WHEN seq_id <= 0;
  END LOOP;

  -- high characters are time based such that hids are in order of creation
  SELECT ABS(FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000)) INTO unix_ms;
  LOOP
    remainder := unix_ms % 36::bigint;
    unix_ms := unix_ms / 36::bigint;
    hid := '' || alphabet[(remainder + 1)] || hid;
  EXIT WHEN unix_ms <= 0;
  END LOOP;

  RETURN UPPER(prefix) || hid;
 END;
 $$;
 COMMENT ON FUNCTION hid_generate IS 'Generates ordered hash ids: alpha-numeric, base36 strings.';
	-- This function generates ordered, base36 alpha-numeric ids similar to Slack's ID scheme.
	--
	-- I wanted a primary key scheme that had the following features:
	-- 1) Lexical order, so that `ORDER BY` works as expected.
	-- 2) Prevents sampling an auto-incrementing primary key to determine growth over time.
	-- 3) Shorter and more human-friendly than BIGINT and UUID keys.
	-- 4) Has a prefix such that table can be inferred from any record's primary or foreign key.
	--
	-- It is suitable for use as primary key, provided a few assumptions are true:
	-- 1) You do not attempt to genereate more than 10M hids per second (system-time).
	-- 2) If system clock is reset backwards, do not violate assumption #1.
	-- 3) System clock is never set to a time prior to Jan 1, 1970.
	--
	-- To use, define your table like:
	-- ```
	-- CREATE SEQUENCE examples_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
	-- CREATE TABLE examples (
	-- id character varying DEFAULT hid_generate('E', 'examples') NOT NULL
	-- )
	-- ```

	CREATE FUNCTION hid_generate(prefix character varying, tbl_name character varying) RETURNS character varying LANGUAGE plpgsql AS $$
	DECLARE
	alphabet constant text[] := string_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'::character varying, null);
	hid character varying := '';
	seq_id bigint;
	unix_ms bigint;
	remainder bigint;
	BEGIN
	-- low characters are sequence based so we can generate up to 10,000 unique hids per millisecond
	SELECT nextval(tbl_name \|\| '_id_seq') % 10000 INTO seq_id;
	LOOP
	remainder := seq_id % 36::bigint;
	seq_id := seq_id / 36::bigint;
	hid := '' \|\| alphabet[(remainder + 1)] \|\| hid;
	EXIT WHEN seq_id <= 0;
	END LOOP;

	-- high characters are time based such that hids are in order of creation
	SELECT ABS(FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000)) INTO unix_ms;
	LOOP
	remainder := unix_ms % 36::bigint;
	unix_ms := unix_ms / 36::bigint;
	hid := '' \|\| alphabet[(remainder + 1)] \|\| hid;
	EXIT WHEN unix_ms <= 0;
	END LOOP;

	RETURN UPPER(prefix) \|\| hid;
	END;
	$$;
	COMMENT ON FUNCTION hid_generate IS 'Generates ordered hash ids: alpha-numeric, base36 strings.';