-----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 ofa
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/7HZ1ood55Uv4ElYRApYCAJ9Bcom1yFl4juaUtLbT968SLfMkMQ CcDb0u
KTg+Zsj1aVjO1ExEjZTYF6c=
=34Uv
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thiemo Kellner <thiemo (AT) thiam (DOT) ch> writes:
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.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thiemo Kellner <thiemo (AT) thiam (DOT) ch> writes:
It's not trying to insert a row of nulls; it's simply not inserting any
row at all. The transformed rule query looks like
INSERT INTO t_songs SELECT ... FROM v_songs WHERE ...
and the WHERE condition is such that no rows will be selected.

No more than selecting from an empty table is invalid, or selecting with
a WHERE condition that matches no rows is invalid.

The fact they don't do what you want doesn't make them worthless ...

There is some discussion of rules vs triggers in the docs:
http://www.postgresql.org/docs/7.4/s...-triggers.html
though I agree that this page is probably fairly unhelpful for
novices, and could stand to be rewritten. In particular it focuses
too much on the question of efficiency and doesn't really explain the
very fundamental semantic differences.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ok, I thought NEW and OLD would be some kind of variables. As the code in the
rule gets rewritten as an other sql statement, I can see that I was
completely misslead. It might be a point to include in the documentation if
it's not already there and I just didn't read carefully enough.

I know. That's not what I meant. I tried to say that if triggers can do
everything rules can but just more, there might not be much reason to keep
rules and confusion. However, the doument of your link below quite clearly
states the differences, quite what I needed and was not able to find in the
documentation (of 7.3 but it is there too). Looking at the chapter it is
supposed to be in some chapter 13.7. However, looking in the index (7.3
interactive) there is only one chapter 13 and that's regression test without
a subchapter 7. Do you know how I can navigate to the document in case I
sould need it some month in the future? I hope I don't bother you too much
going on about documentation. Is there somebody who could explain the
documentation system used at docs.postgresql.org?

Many thanks for your patience

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7LQmood55Uv4ElYRAmzfAKCVZeSI7+58mhGeCT5enI/DOhKEQwCeJE/q
jMDd4HLU/fk5q130Sq1GbEI=
=jytX
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thiemo Kellner <thiemo (AT) thiam (DOT) ch> writes:
I think you're assuming that 7.3 section numbers would apply to 7.4,
which they don't (the html page names are somewhat more likely to carry
across, though).

In 7.3 and before the situation is even more confusing because the docs
are made up of several separate "books" each with its own chapter
numbering. "Rules vs. Triggers" is section 13.7 of the Programmer's
Guide book (in 7.3 anyway), whereas you seem to have been seeking it in
the Administrator's Guide.

7.4 has just one chapter numbering sequence for the entire SGML document
set, which I think is a considerable improvement. We're not going to be
revisiting the 7.3 documentation though. My advice: update to 7.4 ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am Samstag, 27. Dezember 2003 00.09 schrieb Tom Lane:
I am afraid, that I am not:
http://www.postgresql.org/docs/7.3/i...-triggers.html

Ok, true. I rather ignored the Programmer's Guide as it's index shows only
three super chapters. :-(

Not now, at least not with my productive server. I will waint until Gentoo
releases 7.4 in it's "stable" branch.

Thanks

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/7TiQood55Uv4ElYRAhBwAKCDwvZQAbIBL0V/1gkaR57sGvykKgCeNDFN
phOQsryzi8dL+xBFyNSbqF4=
=ZTld
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi guys,

Once again, i´m running into some dificulty with postgres, i need to know if
it possible to encrypt and compile, objects such as functions, and triggers
so that other users can execute it without seing the code it self, if so
could you point me in the right diretion. In SQL server 2000, i would
achieve this using WITH ENCRYPTION command, any help is very much
apreciated, thanks,

Iandé



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Hi guys,

Once again, i´m running into some dificulty with postgres, i need to know if
it possible to encrypt and compile, objects such as functions, and triggers
so that other users can execute it without seing the code it self, if so
could you point me in the right diretion. In SQL server 2000, i would
achieve this using WITH ENCRYPTION command, any help is very much
apreciated, thanks,

Iandé



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Iandé Coutinho wrote:
Sorry -- this has been discussed before, but no one has ever stepped up
to implement it. I might do it myself someday if I can find the time,
but it isn't real high on my list, so don't hold your breath.

Joe



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org