如何在插入时条件下做一些事情

Question

我有两个表，一个是电影列表，另一个是每部电影播放的日期列表。影片列表有name、id、start_date和end_date列，其中id为唯一标识符。

节目列表(有日期的那个)有id, movie_id, date。

每次我INSERT一个新的节目，我希望电影列表更新:如果show.date在movie.start_date之前，我希望start_date更新为show.date的值。结束日期也一样——如果show.date在movie.end_date之后。

下面的规则是我坚持的:(注意:它只会设置开始日期，如果它工作，得到结束日期应该很容易完成，一旦这个工作…)

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"这个错误没有给我(作为一个初学者)任何信息，我错过了一个FROM子句和为什么。

现在，因为每个人似乎都认为规则是一个坏主意(我倾向于屈服于压力)，我尝试了一个触发器，这对我来说绝对是全新的。

使用触发器(强烈推荐)它看起来像这样:

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

紧随其后:

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

这个触发器可能会或可能不会工作，它解决了我的问题，但没有回答我原来的问题…还有谁比我更聪明吗(我当然希望如此!)

Answer 1

where子句中不知道表movies。用途:

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

Answer 2

出现错误消息的原因是您在WHERE子句中引用了表movies。

你可以尝试用这样的查询重写规则:

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;

，但这将不工作与多行INSERT像这样:

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

因为只有movies行和id = 42中的一个日期会被更改。

规则很棘手，很难理解。

你最好有一个触发器