Maverick283 Maverick283 - 2 months ago 7
SQL Question

Postgres Rule: Do something on insert when condition

I'm still quite new to SQL and PostgreSQL especially, so bare with me on this one, please!

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!)?

Answer

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
    );