Why in Postgres, with an empty table, if I use BEFORE INSERT, the :NEW: variable is NULL?-CodePudding

I'm developing a Cinema Database in PostgreSQL. I have a Trigger which, before I insert a new show, checks if the date of the show is prior the movie's release date (for example, adding today a show of Avatar 2). Here's the code:

CREATE TRIGGER beforereleasedate
    BEFORE INSERT
    ON public.shows
    FOR EACH ROW
    EXECUTE PROCEDURE public.filmnotreleased();



CREATE FUNCTION public.filmnotreleased()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE

var1 DATE;
var2 DATE;

BEGIN 

SELECT NEW.ShowDate INTO var1
FROM SHOWS;

SELECT ReleaseDate INTO var2
FROM FILM
WHERE CodF=NEW.CodF;

IF var1<var2 
THEN 
RAISE EXCEPTION 'Can't add a show of a movie still not released.';
END IF;

RETURN NEW;
END;
$BODY$;

ALTER FUNCTION public.filmnotreleased()
    OWNER TO postgres;

Thing is, if the SHOWS table is empty, var1 is NULL. Already checked with a 'raise notice'. The strange part is, this ONLY occurs if the table is empty. So, whatever date i use, the first insert will be wrong. After that, the trigger works properly, and var1 is no longer NULL, but has the value i just inserted.

Any help?

CodePudding user response：

The part: SELECT NEW.ShowDate INTO var1 FROM SHOWS; makes no sense. You are reading all rows from the table shows in order to store the value of one variable (new.showdate) into another variable (var1). And obviously the SELECT won't return any rows if the table is empty which means that var1 will be null.

But there is no need to read anything from the table shows as you have access to the currently inserted rows through the new record.

So you probably want something like this:

CREATE FUNCTION public.filmnonuscito()
    RETURNS trigger
    LANGUAGE plpgsql
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
  var2 DATE;
BEGIN 

  SELECT ReleaseDate 
     INTO var2
  FROM FILM
  WHERE CodF=NEW.CodF;

  IF NEW.showdate < var2 
  THEN 
    RAISE EXCEPTION 'Can't add a show of a movie still not released.';
  END IF;

  RETURN NEW;
END;
$BODY$;

Note that this doesn't handle the situation if there is no such film at all. You might want to add a null check to take care of that, e.g. if NEW.showdate < var2 or var2 is null ...