PostgreSQL - Generate a short GUID in a column

gistfile1.txt

CREATE EXTENSION IF NOT EXISTS "hstore";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS tbl (
  t_id serial PRIMARY KEY
 ,txt text
 ,shortid text
);

CREATE OR REPLACE FUNCTION unique_short_id(
-- Param 1 the column name
)
RETURNS TRIGGER AS $$

 -- Declare the variables we'll be using.
DECLARE
   _col_name text := quote_ident(TG_ARGV[0]);
   _col_value text := to_json(NEW)->>_col_name;  -- no need to escape identifier
  key TEXT;
  qry TEXT;
  found TEXT;
BEGIN
  -- ONLY SEY if there isn't a value in the INSERT
  IF _col_value IS NOT NULL THEN
    RETURN NEW;
  END IF;

  -- generate the first part of a query as a string with safely
  -- escaped table name, using || to concat the parts
  qry := 'SELECT ' || _col_name || ' FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE ' || _col_name || '=';

  -- This loop will probably only run once per call until we've generated
  -- millions of unique short IDs.
  LOOP

    -- Generate our string bytes and re-encode as a base64 string.
    key := encode(public.gen_random_bytes(24), 'base64');

    -- Base64 encoding contains 2 URL unsafe characters by default.
    -- The URL-safe version has these replacements.
    key := replace(key, '/', ''); -- url safe replacement
    key := replace(key, '+', ''); -- url safe replacement
    key := replace(key, '-', ''); -- url safe replacement
    key := replace(key, '_', ''); -- url safe replacement
    key := substring(key from 1 for 10);

    -- Concat the generated key (safely quoted) with the generated query
    -- and run it.
    -- SELECT chatid FROM "test" WHERE chatid='blahblah' INTO found
    -- Now "found" will be the duplicated chatid or NULL.
    EXECUTE qry || quote_literal(key) INTO found;

    -- Check to see if found is NULL.
    -- If we checked to see if found = NULL it would always be FALSE
    -- because (NULL = NULL) is always FALSE.
    IF found IS NULL THEN
      -- If we didn't find a collision then leave the LOOP.
      EXIT;
    END IF;

    -- We haven't EXITed yet, so return to the top of the LOOP
    -- and try again.
  END LOOP;


  -- NEW and OLD are available in TRIGGER PROCEDURES.
  -- NEW is the mutated row that will actually be INSERTed.
  -- We're replacing chatid, regardless of what it was before
  -- with our key variable.
   
   RAISE NOTICE 'shortid: The value of key is >>%<<.', key;

   NEW := NEW #= hstore(_col_name, key);
   
  -- The RECORD returned here is what will actually be INSERTed,
  -- or what the next trigger will get if there is one.
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER demo2
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE unique_short_id('shortid');

INSERT INTO tbl(txt) VALUES ('Amazing new id ' || NOW());
INSERT INTO tbl(txt, shortid) VALUES ('Keep the old boring one ' || NOW(), 'shortid');
SELECT * FROM tbl;