For this particular scenario, can't you just create
two ON DELETE rules? The first would delete from b,
the second from a. Perhaps an example with a scenario
like this can be added to the doc's?

So, the short answer is you can only perform one query
in a rule, but you can have multiple rules defined to
do what you need.

Can you call a stored proc from a rule? You could
pass the old.id to the stored proc and do as many
queries as you like in there without worry that the
old.id would go away.

Just some thoughts. It does suck that old.id goes
away. Any way of preventing that from happening?

later
Rob
--- David Wheeler <david@kineticode.com> 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!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

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

http://www.postgresql.org/docs/faq

On Apr 26, 2005, at 2:43 PM, Rob Butler wrote:

> For this particular scenario, can't you just create
> two ON DELETE rules? The first would delete from b,
> the second from a. Perhaps an example with a scenario
> like this can be added to the doc's?

No, that approach has the same problem. Once the first rule deletes a
record, it's gone from the view, so the second delete wouldn't know how
to do it.

> So, the short answer is you can only perform one query
> in a rule, but you can have multiple rules defined to
> do what you need.

No, you can have multiple queries--you just have to understand that
those that come first might have an effect on those that come later.

> Can you call a stored proc from a rule? You could
> pass the old.id to the stored proc and do as many
> queries as you like in there without worry that the
> old.id would go away.

Yes, that would be one solution. Another would be to have an ON DELETE
CASCADE on the foreign key constraint. Then, to delete the record from
both tables, you just delete it from the primary key table.

> Just some thoughts. It does suck that old.id goes
> away. Any way of preventing that from happening?

Doesn't sound like it. But your suggestion to use a function is a good
one. (Although Tom did say something about volatile functions...).

Regards,

David


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

Rob Butler <crodster2k@yahoo.com> writes:
> For this particular scenario, can't you just create
> two ON DELETE rules? The first would delete from b,
> the second from a. Perhaps an example with a scenario
> like this can be added to the doc's?

No, that doesn't work any more than the other way.

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

David Wheeler <david@kineticode.com> writes:
> No, you can have multiple queries--you just have to understand that
> those that come first might have an effect on those that come later.

.... which indeed can be a feature, not a bug, depending on what you're
doing ...

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

On Apr 26, 2005, at 2:58 PM, Tom Lane wrote:

> ... which indeed can be a feature, not a bug, depending on what you're
> doing ...

Absolutely. An INSERT rule I have looks like this:

CREATE RULE insert_one AS
ON INSERT TO one WHERE NEW.id IS NULL
DO INSTEAD (
INSERT INTO _simple (id, guid, state, name, description)
VALUES (NEXTVAL('seq_kinetic'), NEW.guid, NEW.state, NEW.name,
NEW.description);

INSERT INTO simple_one (id, bool)
VALUES (CURRVAL('seq_kinetic'), NEW.bool);
);

The call to NEXTVAL() in the first statement sets up a value I use in
the second via CURRLVA().

Cheers,

David


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

On 4/26/2005 5:58 PM, Tom Lane wrote:
> David Wheeler <david@kineticode.com> writes:
>> No, you can have multiple queries--you just have to understand that
>> those that come first might have an effect on those that come later.
>
> ... which indeed can be a feature, not a bug, depending on what you're
> doing ...
>
> regards, tom lane

There is no such thing as a free lunch here. If one wants a row inserted
by one action being visible by a subsequent one, then a delete done in
another action must (by default) be visible to subsequent actions as
well. I don't think that fiddling with scan-command-ID's on top of the
rule definitions will make the average user understand them easier.

The multi-action rules usually come into play when someone attempts to
make join-views updatable. Not an easy problem, granted, but most of the
time I have found a combination of rules together with ON UPDATE/DELETE
CASCADE constraints or even user defined triggers absolutely sufficient.
The INSERT and UPDATE case is handled by rules as usual. And in the
DELETE case the rule just deletes the critical rows and the ON DELETE
CASCADE constraints do the rest.


Jan

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = JanWieck@Yahoo.com #

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

On Apr 26, 2005, at 5:02 PM, Jan Wieck wrote:

> The multi-action rules usually come into play when someone attempts to
> make join-views updatable. Not an easy problem, granted, but most of
> the time I have found a combination of rules together with ON
> UPDATE/DELETE CASCADE constraints or even user defined triggers
> absolutely sufficient. The INSERT and UPDATE case is handled by rules
> as usual. And in the DELETE case the rule just deletes the critical
> rows and the ON DELETE CASCADE constraints do the rest.

Yes, this is what I'm finding, too. But it would be good if the
documentation better reflected that this is how it works.

Regards,

David


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings