On 4/26/2005 3:01 PM, Rob Butler wrote:
Are rules even needed anymore? Can't you do this all with triggers? If you want to "DO INSTEAD" just use a row based trigger, and return null. Or is this less efficient?
On INSERT, yes, on UPDATE, how so?
Jan
Later Rob --- David Wheeler <[EMAIL PROTECTED]> wrote:On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
> Well, they handle simple situations OK, but we
keep seeing people get
> burnt as soon as they venture into interesting
territory. For > instance,
> if the view is a join, you can't easily make a
rule that turns a delete
> into deletions of both joined rows. And you'll
get burnt if you try to
> insert any volatile functions, because of the
multiple-evaluation > issue.
> Etc.
sharky=# CREATE TABLE a ( sharky(# id int, sharky(# name text sharky(# ); CREATE TABLE sharky=# CREATE TABLE b ( sharky(# a_id int, sharky(# rank text sharky(# ); CREATE TABLE sharky=# sharky=# CREATE VIEW ab AS sharky-# SELECT id, name, rank sharky-# FROM a, b sharky-# WHERE a.id = b.a_id sharky-# ; CREATE VIEW sharky=# CREATE RULE delete_ab AS sharky-# ON DELETE TO ab DO INSTEAD ( sharky(# DELETE FROM b sharky(# WHERE a_id = OLD.id; sharky(# sharky(# DELETE FROM a sharky(# WHERE id = OLD.id; sharky(# ); CREATE RULE sharky=# sharky=# sharky=# insert into a values (1, 'test'); INSERT 597795 1 sharky=# insert into b values (1, 'sergeant'); INSERT 597796 1 sharky=# select * from ab; id | name | rank ----+------+---------- 1 | test | sergeant (1 row)
sharky=# delete from ab; DELETE 0 sharky=# select * from ab; id | name | rank ----+------+------ (0 rows)
sharky=# select * from a; id | name ----+------ 1 | test (1 row)
sharky=# select * from b; a_id | rank ------+------ (0 rows)
Ah, yes, you're right, that is...unexpected. Perhaps
OLD can contain its values for the duration of the RULE's
statements? I'm assuming that what's happening is that OLD.id is NULL after the
first of the two DELETE statements...
> Like I said, I don't have a better idea. Just a vague feeling of > dissatisfaction.
I'd call it a bug. ;-)
Regards,
David
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
