Random Key for Check Ins with a PostgreSQL database

random_key.sql

-- Example table for check ins (adjust to use real tables)
-- Table: check_ins

-- DROP TABLE check_ins;

CREATE TABLE check_ins
(
  check_in_id serial NOT NULL,
  check_in_key character varying(20) NOT NULL,
  note text NULL,
  user_id bigint NOT NULL,
  place_id bigint NOT NULL,
  creation_date time with time zone NOT NULL DEFAULT now(),
  modified_date time with time zone NOT NULL DEFAULT now(),
  CONSTRAINT "check_in_pkey" PRIMARY KEY (check_in_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE check_ins
  OWNER TO brennan;

-- Function: random_key(integer)

-- DROP FUNCTION random_key(integer);

CREATE OR REPLACE FUNCTION random_key(length integer)
  RETURNS text AS
$BODY$
declare
  i integer := 0;
  chars text[] := '{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}';
  result text := '';
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION random_key(integer)
  OWNER TO brennan;

-- select random_key(12);
--select random_key(12) from generate_series(1,10);

create or replace function fetch_random_check_in_key() returns character varying(20) as 
$$
declare
  new_check_in_key character varying(20) := '';
  is_done boolean := false;
begin

  while not is_done loop
    new_check_in_key := (select random_key(12));
    is_done := not exists (select * from check_ins where check_in_key = new_check_in_key);
  end loop;
  
  return new_check_in_key;
end;
$$ language plpgsql;

-- select fetch_random_check_in_key();

create or replace function store_check_in(note text, user_id bigint, place_id bigint) returns character varying(20) as 
$$
declare
  new_check_in_key character varying(20) := '';
  is_done boolean := false;
begin

  new_check_in_key := fetch_random_check_in_key();
  insert into check_ins (check_in_key, note, user_id, place_id) values (new_check_in_key, note, user_id, place_id);
  
  return new_check_in_key;
end;
$$ language plpgsql;

-- select store_check_in('First time!', 1, 1);
-- select store_check_in('Second time!', 1, 1);
-- select * from check_ins;
-- delete from check_ins;