CREATE TABLE rps (
    rpkey character(32) NOT NULL,
    rpid text UNIQUE NOT NULL
);

-- above create table creates rps_rpid_key automatically
-- CREATE INDEX rps_rpid_key ON rps (rpid);
CREATE INDEX rps_rpkey_idx ON rps (rpkey);

REVOKE ALL ON TABLE rps FROM PUBLIC;
GRANT ALL ON TABLE rps TO shibadmin;
GRANT INSERT,SELECT ON TABLE rps TO shib;

CREATE TABLE tgtids (
    regid character(32),
    rpkey character(32),
    tgtid character(32),
    UNIQUE (regid, rpkey)
);

-- above create table creates tgtids_regid_key automatically
-- CREATE INDEX tgtids_regid_key ON tgtids (regid, rpkey);

REVOKE ALL ON TABLE tgtids FROM PUBLIC;
GRANT ALL ON TABLE tgtids TO shibadmin;
GRANT INSERT,SELECT ON TABLE tgtids TO shib;

-- tid(regid,rpid)  returns tgtid 

CREATE FUNCTION tid(character, text) RETURNS character
    AS '
declare
  my_tgtid char(32);
  my_rpkey char(32);
begin
  select into my_tgtid tgtid from tgtids,rps where regid=$1 and rps.rpid=$2 and tgtids.rpkey=rps.rpkey;
  if not found then
     select into my_rpkey rpkey from rps where rpid=$2;
     if not found then
         select into my_rpkey md5($2);
         INSERT INTO rps values (my_rpkey, $2);
     end if;
     select into my_tgtid md5(''random_secret_string''||$1||my_rpkey||''nother_secret_string'');
     INSERT INTO tgtids values ($1, my_rpkey, my_tgtid);
     if not found then
         return my_tgtid;
     end if;
  end if;
  return my_tgtid;
end;
'
    LANGUAGE plpgsql;