I wrote a stored procedure that allows a join entity to be created by name for my fictional characters database. I would like some feedback on it with regard to exception handling, and conditions I didn't think of. Also I would like to know if this procedure is good as far as performance goes.
The tables it involves are
character_media_actor. The schemas are as follows.
CREATE TABLE medias ( med_unv_id INTEGER NOT NULL REFERENCES universes (unv_id), med_title VARCHAR(255) NOT NULL, med_release_date DATE, med_type INTEGER REFERENCES media_types (mt_id), med_id SERIAL PRIMARY KEY, med_attribs JSON ); CREATE OR REPLACE VIEW v_medias AS SELECT med_unv_id, med_title, med_release_date, mt_name as type, med_id, med_attribs, mt_uses_actors FROM medias m JOIN media_types mt ON mt.mt_id = m.med_type; CREATE TABLE actors ( act_id SERIAL PRIMARY KEY, act_name VARCHAR(255) NOT NULL, act_birth DATE, act_death DATE, act_imdb VARCHAR(4096) UNIQUE, act_bio TEXT, is_locked BOOLEAN DEFAULT FALSE, CHECK (act_birth < act_death) ); DROP TABLE IF EXISTS characters CASCADE; CREATE TABLE characters ( chr_unv_id INTEGER NOT NULL REFERENCES universes (unv_id), chr_name VARCHAR(255) NOT NULL, chr_id SERIAL PRIMARY KEY, chr_bio TEXT, UNIQUE(chr_name,chr_unv_id) ); CREATE TABLE character_media_actors ( cma_act_id INTEGER REFERENCES actors (act_id), cma_chr_id INTEGER NOT NULL REFERENCES characters (chr_id), cma_med_id INTEGER NOT NULL REFERENCES medias (med_id), cma_id SERIAL PRIMARY KEY, UNIQUE(cma_act_id,cma_chr_id,cma_med_id) );
The stored procedure is as follows:
CREATE OR REPLACE FUNCTION add_cma_by_names( IN p_med_title VARCHAR(255), IN p_act_name VARCHAR(255), IN p_chr_name VARCHAR(255) ) RETURNS VOID AS $$ DECLARE lk_act_id INTEGER := (SELECT act_id FROM actors WHERE LOWER(act_name) = LOWER(p_act_name)); lk_chr_id INTEGER := NULL; lk_med_id INTEGER := NULL; md_unv_id INTEGER := NULL; cr_unv_id INTEGER := NULL; mt_actors BOOLEAN; BEGIN -- perform validation first IF p_med_title IS NULL OR LENGTH(p_med_title) = 0 THEN RAISE EXCEPTION 'A media title must be given'; END IF; IF p_chr_name IS NULL OR LENGTH(p_chr_name) = 0 THEN RAISE EXCEPTION 'A character name must be given'; END IF; -- load the media and universe ids in one go SELECT med_unv_id, med_id, mt_uses_actors INTO md_unv_id, lk_med_id, mt_actors FROM v_medias WHERE LOWER(p_med_title) = LOWER(med_title); -- we require the universe id before we can get the character id SELECT chr_id, chr_unv_id INTO lk_chr_id, cr_unv_id FROM characters WHERE LOWER(p_chr_name) = LOWER(chr_name); IF mt_actors IS TRUE AND lk_act_id IS NULL THEN RAISE EXCEPTION 'Selected media has actors must not be null: actor = %', p_act_name; END IF; IF lk_med_id IS NULL THEN RAISE EXCEPTION 'Media Not Found, name = %', p_med_title; END IF; IF lk_chr_id IS NULL THEN RAISE EXCEPTION 'Character Not Found, name = %', p_chr_name; END IF; IF md_unv_id <> cr_unv_id THEN RAISE EXCEPTION 'Character and Media must be in same universe'; END IF; INSERT INTO character_media_actors (cma_act_id, cma_chr_id, cma_med_id) VALUES (lk_act_id, lk_chr_id, lk_med_id); RETURN; END $$ LANGUAGE PLPGSQL;