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;