functions.sql

/* Make Jobs Searchable */

DROP TRIGGER make_job_searchable ON jobs;
DROP TRIGGER make_job_searchable_update ON jobs;
DROP FUNCTION make_job_searchable();

CREATE FUNCTION make_job_searchable() RETURNS trigger AS $make_job_searchable$
	DECLARE
		searchable_document text;
		industries text;
		categories text;
		company_data text;
		tags text;
		
	BEGIN
		searchable_document := NEW.job_title;

		IF NEW.job_description IS NOT NULL THEN
			searchable_document := searchable_document || ' ' || NEW.job_description;
		END IF;

		IF NEW.zip_code IS NOT NULL THEN
			searchable_document := searchable_document || ' ' || NEW.zip_code;
		ELSE
			searchable_document := searchable_document || ' ' || NEW.city || ' ' || NEW.state;
		END IF;

		SELECT company_name || ' ' || company_description FROM companies WHERE company_id = NEW.company_id INTO company_data;

		IF json_array_length(NEW.industries) != 0 THEN
			SELECT string_agg(trim(industry_elem::text, '"'), ' ')
				FROM jobs, json_array_elements(jobs.industries) industry_elem
				WHERE jobs.job_id = NEW.job_id
				INTO industries;
			searchable_document := searchable_document || ' ' || industries;
		END IF;

		IF json_array_length(NEW.categories) != 0 THEN
			SELECT string_agg(trim(category_elem::text, '"'), ' ')
				FROM jobs, json_array_elements(jobs.categories) category_elem
				WHERE jobs.job_id = NEW.job_id
				INTO categories;
			searchable_document := searchable_document || ' ' || categories;
		END IF;

		IF json_array_length(NEW.tags) != 0 THEN
			SELECT string_agg(trim(tag_elem::text, '"'), ' ')
				FROM jobs, json_array_elements(jobs.tags) tag_elem
				WHERE jobs.job_id = NEW.job_id
				INTO tags;
			searchable_document := searchable_document || ' ' || tags;
		END IF;

		NEW.searchable = to_tsvector(searchable_document);

		RETURN NEW;
	END;
$make_job_searchable$ LANGUAGE plpgsql;

CREATE TRIGGER make_job_searchable
	AFTER INSERT ON jobs
	FOR EACH ROW
	EXECUTE PROCEDURE make_job_searchable();

CREATE TRIGGER make_job_searchable_update
	BEFORE UPDATE ON jobs
	FOR EACH ROW
	EXECUTE PROCEDURE make_job_searchable();