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
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);
ERROR: missing FROM-clause entry for table "movies"
CREATE OR REPLACE FUNCTION adjust_start_date()
RETURNS trigger AS
UPDATE movies SET start_date = NEW.date
WHERE id = NEW.movie_id AND start_date > NEW.date;
CREATE TRIGGER adjust_start_date_trigger
FOR EACH ROW
EXECUTE PROCEDURE adjust_start_date();
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 );