Hey all! I've been racking my brain on this all morning. The function below is intended to create a function that returns a "shortkey" similar to the way YouTube handles video ids (

eTsiE3tmnI4

for example). The function is run as the default value on a column in my dB I'm getting some kind of syntax error, but there are little to no details in the error. Does anybody see anything wrong with it?? Thanks!!

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE OR REPLACE FUNCTION generate_shortkey()
RETURNS TEXT AS 
$$
DECLARE
  gkey TEXT;
  key TEXT;
  qry TEXT;
  found TEXT;
BEGIN
  -- generate the first part of a query as a string with safely
  -- escaped table name, using || to concat the parts
  qry := 'SELECT shortkey FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE shortkey=';

  LOOP
    -- 8 bytes gives a collision p = .5 after 5.1 x 10^9 values
    gkey := encode(gen_random_bytes(8), 'base64');
    gkey := replace(gkey, '/', '_');  -- url safe replacement
    gkey := replace(gkey, '+', '-');  -- url safe replacement
    key := rtrim(gkey, '=');          -- cut off padding

    -- Concat the generated key (safely quoted) with the generated query
    -- and run it.
    -- SELECT id FROM "test" WHERE id='blahblah' INTO found
    -- Now "found" will be the duplicated id 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;

  RETURN key
END
$$ language 'plpgsql';

You should still show whatever error you are getting. Have you looked in database postgres logs? Also you might need "SET search_path = extensions, public, pg_temp" in your function definition.

Yeah so the error only says

syntax error at end of input

and the postgres logs say the same

Ah you are getting an error setting up the function... Maybe need a semicolon after END;

missing a semicolon on

RETURN key

Thank you for the extra set of eyes!!