-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I have a view that I want to make updateble, at least kind of. What should
happen on update (trying to implement in one rule):
- - make the original record invisible in the view by changing the value of a
flag in the table underlying table
- - insert a new record into the underlying table
- - update another table (I have not come that far)
Therefore I created following rule:
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. So I changed to the rule for
testing into:
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 (
'rübenkraut',1,2,null
)
)
;
An update now results in:
psql:data.pgsql:124: ERROR: Cannot update a view
You need an unconditional ON UPDATE DO INSTEAD rule
Why? I cannot see where my rule is some kind of conditional. I couldn't find
any hint, neither in the docs, nor googling, nor metacrawling.
Anybody an idea?
Cheers
Thiemo
- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQE/7HZ1ood55Uv4ElYRApYCAJ9Bcom1yFl4juaUtLbT968SLfMkMQCcDb0u
KTg+Zsj1aVjO1ExEjZTYF6c=
=34Uv
-----END PGP SIGNATURE-----
