Subject: Trouble with rules

From: [email-masked] (Paddy Hodgson)
Date: Thu, 30 Apr 92 02:24:37 -0700

Cheers, 
This is a resend of a query that went off before Easter and may have 
got lost.   If not - sorry to trouble you again. 

I am having some problems in using rules.   The first problem concerns 
a difference between  instance and rewrite rules.  The second is in 
interpreting the result of a rule.   I would be grateful for advice.

THE TABLE BEING USED IS FROM THE DEMO DATABASE E.G.
==================================================
* retrieve (emp.all) \g

Query sent to backend is "retrieve (emp.all) "
-----------------------------------------------------------------------
| salary      | manager     | location    | age         | name        |
-----------------------------------------------------------------------
| 1000        | sharon      | (11,10)     | 20          | bill        |
-----------------------------------------------------------------------
| 1000        | sam         | (15,12)     | 25          | joe         |
-----------------------------------------------------------------------
| 80          | bill        | (10,5)      | 30          | sam         |
-----------------------------------------------------------------------

THE REWRITE RULE USED IS 
========================

   define rewrite rule q3rule_1 is
      on append to emp
   do instead
      retrieve (e.all)
        from e in emp 
        where e.name = new.manager

I would expect the following APPEND query to act like
    retrieve (emp.all) where e.name = "x"
and produce no instances - however I get 2 which seem to fit the query    
    retrieve (e.all) from e in emp   where e.name = emp.manager
it seems that 'new' is the whole emp table and not just the instance
in the APPEND query.

* append emp(name="a", manager="x")  \g

Query sent to backend is "append emp(name="a", manager="x")  "
-----------------------------------------------------------------------
| salary      | manager     | location    | age         | name        |
-----------------------------------------------------------------------
| 1000        | sharon      | (11,10)     | 20          | bill        |
-----------------------------------------------------------------------
| 80          | bill        | (10,5)      | 30          | sam         |
-----------------------------------------------------------------------

THE FOLLOWING QUERY DOES THE SAME THING.

* append emp(name="a", manager="sam")  \g

2. The rule was changed from a rewrite to an instance rule and 
    caused retrieve to be rejected with a warning about parameters
    as below.   Removing the rule allowed retrieve to work again.
--------------------------------
define rule q3rule_1 is
on append to emp
do instead
retrieve (e.all)
  from e in emp 
  where e.name = new.manager
* \g

retrieve (emp.all) \g

Query sent to backend is "retrieve (emp.all) "
WARN:Apr 16 18:06:14:View Rules must not have parameters!
----------------------------------------------------------

| D P Hodgson            | e-mail: [email-masked]
| Computer Science, MPS  |
| Murdoch University     | fax:   (09) 310 1711
| Murdoch                | phone: (09) 360 2794
| W. Australia   6150    |

---

From: postarch (Postgres Mailing Archive)
Date: Mon, 04 May 92 15:25:06 PDT

> * append emp(name="a", manager="sam")  \g

As near as I can tell this is a bug in the rewrite system.  A slight
modification to your above rule works in the instance level system
(see below).

>     as below.   Removing the rule allowed retrieve to work again.

[ This from Spyros, The "Official" Postgres Rules personality ]

a) "on append .... do retrieve" rules are not allowed.
In general, a "retrieve" can appear in the action of a rule ONLY if the
event that triggers the rule is a "retrieve" too.
(exception: a "retrieve into <relation>" can appear in the action of
any rule).

[ I tried to run the exception case and ran into several problems, which
  I spent some time fixing.  I don't think it will work in 3.1, but you
  can try it. -Jeff ]

b) yes, there is a bug! The system should have complained when the rule
was defined (instead of waiting till you actually fire the rule)

Spyros.