CODEWITHSUNDEEP
sqlpostgresql
Maverick283
Maverick283

Reputation: 1403

How to do something on insert when condition

I have two tables, one with a list of movies, and one with a list of dates when each movie is played. The movies list has the columns name, id, start_date and end_date, while id is a unique identifier.

The shows list (the one with the dates) has id,movie_id,date.

Every time I INSERT a new show, I'd like the movies list to be updated: If the show.date is before the movie.start_date, I'd like the start_date to be updated to the value of the show.date. Same goes for the end date - obviously if the show.date is after the movie.end_date.

The following rule is what I am stuck with: (NOTE: It would only set the start date if it worked, getting the end date done should be easy once this works...)

CREATE RULE "movies_start_date_setter" AS ON INSERT TO "shows"
WHERE movies.id = NEW.movie_id AND movies.start_date < NEW.date
DO (UPDATE movies SET start_date = NEW.date);

It returns: ERROR: missing FROM-clause entry for table "movies" This error doesn't give me (as a beginner) any information where I'm missing a FROM clause and why.

Now since everybody seems to think a rule is a bad idea (and I tend to bow the pressure) I tried a trigger, which is absolutely totally new to me.

Using a trigger (highly recommended) it would look something like this:

CREATE OR REPLACE FUNCTION adjust_start_date()
RETURNS trigger AS
$BODY$
BEGIN
    UPDATE movies SET start_date = NEW.date
    WHERE id = NEW.movie_id AND start_date > NEW.date;    
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';

Followed by:

CREATE TRIGGER adjust_start_date_trigger
   AFTER INSERT
   ON shows
   FOR EACH ROW
   EXECUTE PROCEDURE adjust_start_date();

This trigger may or may not work, it solves my issue but doesn't answer my original question... Anybody else out there smarter than me (I sure hope so!)?

Upvotes: 2

Views: 139

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247235

The reson for the error message is that you reference table movies in the WHERE clause.

You could try with a query rewrite rule like this:

CREATE RULE movies_start_date_setter AS
   ON INSERT TO shows DO ALSO
   UPDATE movies
      SET start_date = LEAST(NEW.date, start_date),
          end_date = GREATEST(NEW.date, end_date)
      WHERE id = NEW.movie_id
        AND NEW.date NOT BETWEEN start_date AND end_date;

but that won't work with multi-line INSERTs like this:

INSERT INTO shows (id, movie_id, date) VALUES
    (1, 42, <very small date>),
    (2, 42, <very large date>);

because only one of the dates in the movies row with id = 42 will get changed.

Rules are tricky and hard to understand.

You are better off with a trigger.

Upvotes: 0

klin
klin

Reputation: 121754

The table movies is not known in where clause. Use:

create rule movies_start_date_setter as 
on insert to shows do (
    update movies 
    set start_date = new.date
    where id = new.movie_id and start_date > new.date
    );

Upvotes: 1

Related Questions