Thread: RE: [SQL] nextval

> On Thu, 28 May 1998, Jerome ALET wrote:
>
> >
> > Yes, I already know that solution, but I wondered why mine was bad
> (the
> > forgotten '') and finally I think I've found a (maybe already known)
> bug
> > in CREATE RULE (look at the results in my previous message).
> >
>
> Who hasn't already found one? :)
>
> the man page for create_rule clearly says
>
> DESCRIPTION
>        The current rule system implementation is very brittle and
>        is unstable.  Users are discouraged from  using  rules  at
>        this time.
>
I wouldn't consider the results that Jerome is getting to be a bug.
What is happening is that the RULE is inserting the new value into every
row each time he inserts a new row.  A test to verify that would be to
insert two rows then a select and see if the behavior is consistent with
your previous assumptions.
Now with that said the solution would be to somehow limit your insert
rule to the currently inserted row.  I haven't played with rules enough
(read: at all), so I can't give you the sql, but I bet it looks like a
WHERE clause.

Hope this helps,
    -DEJ

Jackson, DeJuan wrote:
> I wouldn't consider the results that Jerome is getting to be a bug.
> What is happening is that the RULE is inserting the new value into every
> row each time he inserts a new row.  A test to verify that would be to
> insert two rows then a select and see if the behavior is consistent with
> your previous assumptions.

OK, I'll try this tomorrow. I haven't tested this TEST :)

but when I read the doc I understand (I'm not English nor American)
that in the manual page:

   create rule rule_name
           as on event
             to object [where clause]
           do [instead]
           [action | nothing | [actions...]]

the WHERE is optional and that in:

           new or current can appear instead  of  an  instance
              variable whenever an instance variable is permissi-
              ble in SQL.

       The semantics of a rule is that at the time an  individual
       instance  is accessed, updated, inserted or deleted, there
       is a current instance (for retrieves, updates and deletes)
       and  a  new  instance  (for  updates and appends).  If the
       event specified in the on clause and the condition  speci-
       fied  in  the  where  clause  are  true  for  the  current
       instance, then the action part of the  rule  is  executed.
       First, however, values from fields in the current instance
       and/or the new instance are substituted for:
       current.attribute-name
       new.attribute-name

the rule should apply one tuple at a time.

maybe I was misunderstanding the manual page.

thanks all
Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE