Re: Why should my rule be conditional? - Mailing list 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