Creating a join table entry by looking up name
I'm not a postgres expert by any means, so take this as just an opinion:


DECLARE
  lk_act_id INTEGER := (SELECT act_id
                        FROM actors
                        WHERE LOWER(act_name) = LOWER(p_act_name));



Several issues with this.

I don't think the DECLARE section should be used for querying a table. I think it should only perform literal assignments. (I don't know if there's any accepted guidance around this, but it seems wrong to me.)

LOWER(act_name) will ensure any index on that column isn't used. Granted it's not an issue in your code because you don't have an index, but in the real world you probably would (or you'd suffer the performance impact of a sequential scan on that table every time the function is called, which would become an issue with a large table).

act_name has no guarantee of uniqueness - and it shouldn't, as there can certainly be more than one actor with the same name. So the above would throw an exception if it found more than one result. What then?

The query runs before the validation. If validation subsequently fails, you've done a sequential scan for nothing.


lk_chr_id INTEGER := NULL;
lk_med_id INTEGER := NULL;
md_unv_id INTEGER := NULL;
cr_unv_id INTEGER := NULL;



None of these variables need to be explicitly set to NULL, as all variables not explicitly initialised are NULL by default. (I'm not sure if this holds for all types e.g. enums, but certainly for all "native" and custom types I've used, it holds.)


  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;



What if it's whitespace? I'd do something like this instead:

IF NULLIF(TRIM(p_med_title), '') IS NULL THEN...

Although, I might cache the trimmed inputs so I don't have to trim them again when using them in the subsequent queries.

Also you could use ELSIF rather than having two separate IF statements.

You should probably also validate p_act_name here before looking it up in the table, or if it's valid for this parameter to be null or empty, then check it's not null/empty before querying the table for the id.



As to the rest, I can't quite understand what exactly it's trying to do and what all the conditions are. I would prefer seeing the desired outcome and constraints written in plain English rather than working it out from the code.