Re: Why should my rule be conditional?
От | Tom Lane |
---|---|
Тема | Re: Why should my rule be conditional? |
Дата | |
Msg-id | 26606.1072463281@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Why should my rule be conditional? (Thiemo Kellner <thiemo@thiam.ch>) |
Ответы |
Re: Why should my rule be conditional?
(Thiemo Kellner <thiemo@thiam.ch>)
|
Список | pgsql-novice |
Thiemo Kellner <thiemo@thiam.ch> writes: > CREATE OR REPLACE RULE r_v_songs_upd > AS ON UPDATE TO v_songs > DO INSTEAD ( > UPDATE t_songs > SET > show = FALSE > WHERE > NEW.song_id = song_id > ; > INSERT INTO t_songs ( > song_title, > year_of_composition, > year_of_first_publication, > predecessor_id > ) VALUES ( > NEW.song_title, > NEW.year_of_composition, > NEW.year_of_first_publication, > NEW.song_id > ) > ) > ; > If I do the update on v_songs, the update part of the rule gets executed fine, > but the insert does not seem to do anything. The above looks like a dead end to me; you can't make it work, and the reason is that OLD and NEW are defined with reference to the view. Once you do the UPDATE, that row is no longer visible in the view (correct?) and so there is no NEW row and the INSERT doesn't do anything. Think of the INSERT as being rewritten into an "INSERT ... SELECT ... FROM view" sort of construct, and you'll see why. A gross hack comes to mind: CREATE OR REPLACE RULE r_v_songs_upd AS ON UPDATE TO v_songs DO INSTEAD ( INSERT INTO t_songs ( song_title, year_of_composition, year_of_first_publication, predecessor_id, show ) VALUES ( NEW.song_title, NEW.year_of_composition, NEW.year_of_first_publication, NEW.song_id, NULL ) ; UPDATE t_songs SET show = (CASE WHEN show IS NULL THEN TRUE ELSE FALSE END) WHERE NEW.song_id = song_id ) ; but I think I'd recommend looking into using a trigger instead. The above looks pretty fragile in the presence of concurrent updates, to name just one problem. Triggers are notationally more daunting than rules, but conceptually they are a lot simpler; you're only dealing with one row at a time, and it can't change underneath you. Most of the things I see people trying to use rules for would be better accomplished with a trigger. > An update now results in: > psql:data.pgsql:124: ERROR: Cannot update a view > You need an unconditional ON UPDATE DO INSTEAD rule > Why? Not sure; could be a bug, but without a complete reproducible example I'm more inclined to blame pilot error. Is t_songs itself a view? regards, tom lane
В списке pgsql-novice по дате отправления: