CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION {SCHEMA_NAME}.find_or_create_token(job_id BIGINT) RETURNS VARCHAR AS $$ DECLARE

v_token UUID;

BEGIN

SELECT token INTO v_token FROM postjob.tokens WHERE postjob_id=job_id;

if v_token IS NULL THEN
  SELECT gen_random_uuid() INTO v_token;
  INSERT INTO postjob.tokens(postjob_id, token) VALUES(job_id, v_token);
END IF;
RETURN v_token::varchar;

END; $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION {SCHEMA_NAME}.postjobs_by_token(v_token UUID)

RETURNS SETOF {SCHEMA_NAME}.postjobs AS $$

BEGIN

RETURN QUERY
  SELECT {SCHEMA_NAME}.postjobs.* FROM {SCHEMA_NAME}.postjobs
  INNER JOIN {SCHEMA_NAME}.tokens ON {SCHEMA_NAME}.tokens.postjob_id={SCHEMA_NAME}.postjobs.id
  WHERE {SCHEMA_NAME}.tokens.token=v_token;

END; $$ LANGUAGE plpgsql;