Postgres Pro
Downloads
Home   >   mailing lists

Thread: RULES

RULES

From
"J.Fernando Moyano"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Well ... i'm testing carefully the two rules ==>

CREATE RULE piezas_add AS        ON insert TO piezas        DO update materia_prima set usadas=(usadas+1)        where
n_material=new.n_material;

CREATE RULE piezas_delete AS        ON delete TO piezas        DO update materia_prima set usadas=(usadas-1)
wheren_material=old.n_material;
 

Inserting is OK.
Deleting is broken if it deletes more than one row. The rule is executed only 
one time each delete command, and not one time each deleted row.

It's this OK ??? Am i wrong ???
It's a "feature" ??  ;-)

Thanks

- -- 
Fernando Moyano

Frase del día:
- --------------
¡CocaCola esasín! ¡prrrrrrrrrts, bruuuuuuuups! 

(*) SymeX ==> http://symex.lantik.com
(*) WDBIL ==> http://wdbil.sourceforge.net
(*) Informate sobre LINUX en http://www.linux.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7Cp+HoZaf9MvtDvcRAqV/AKCQLhl34VF3sfPt3O5i5w5MFpcZRQCgmm5b
duRciSRJhzTJLuhFrNLUcWQ=
=It2U
-----END PGP SIGNATURE-----

Re: RULES

From
Martín Marqués
Date:
On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Well ... i'm testing carefully the two rules ==>
>
> CREATE RULE piezas_add AS
>          ON insert TO piezas
>          DO update materia_prima set usadas=(usadas+1)
>          where n_material=new.n_material;
>
> CREATE RULE piezas_delete AS
>          ON delete TO piezas
>          DO update materia_prima set usadas=(usadas-1)
>          where n_material=old.n_material;
>
> Inserting is OK.
> Deleting is broken if it deletes more than one row. The rule is executed
> only one time each delete command, and not one time each deleted row.
>
> It's this OK ??? Am i wrong ???
> It's a "feature" ??  ;-)

That's the right behaviour!
Each time you do a delete, besides the delete, it will execute the update....
If more then one row is deletes, update.... gets executed only once.

You'll have to do more advance programing there. See if count(*) can help you 
in any way.
I see you have to update usadas=(usadas-1). Try something like:

usadas=(usadas-(select count(*) from piezas WHERE condition_delete) )

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------

Re: RULES

From
"Ross J. Reedstrom"
Date:
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote:
> On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> > Deleting is broken if it deletes more than one row. The rule is executed
> > only one time each delete command, and not one time each deleted row.
> >
> > It's this OK ??? Am i wrong ???
> > It's a "feature" ??  ;-)
> 
> That's the right behaviour!
> Each time you do a delete, besides the delete, it will execute the update....
> If more then one row is deletes, update.... gets executed only once.
> 

Rules are rewrites of the SQL query, before it gets planned and
optimized, so they are good for things that need to happen once per
statement. Triggers, on the other hand, fire per tuple, when the actual
bits are going into/out of the underlying table. They're good for things
that need to happen per tuple.  

See Bruce's book for the basics:

http://www.ca.postgresql.org/docs/aw_pgsql_book/node166.html

Ross