Обсуждение: SQL Rule

Hi list

I have a table construction like the one seen below, when i am updating
or inserting i get a recurion, logical. But how to manage it that the
rule is just doing it one time. Or is it possible to do the sum of a
and b in an other way?

CREATE TABLE test
(
  a int2,
  b int2,
  c int2,
  id int2 NOT NULL,
  CONSTRAINT id_test PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE test OWNER TO postgres;


CREATE OR REPLACE RULE sum_op AS
    ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
  WHERE test.id = new.id;

CREATE OR REPLACE RULE sum_op_up AS
    ON UPDATE TO test DO  UPDATE test SET c = test.a + test.b
  WHERE test.id = new.id;

Could you create the table without the C column
then create a view test_view with
select a,b,a+b as c,id from test;


Oisin

Bert wrote:
> Hi list
>
> I have a table construction like the one seen below, when i am updating
> or inserting i get a recurion, logical. But how to manage it that the
> rule is just doing it one time. Or is it possible to do the sum of a
> and b in an other way?
>
> CREATE TABLE test
> (
>   a int2,
>   b int2,
>   c int2,
>   id int2 NOT NULL,
>   CONSTRAINT id_test PRIMARY KEY (id)
> )
> WITHOUT OIDS;
> ALTER TABLE test OWNER TO postgres;
>
>
> CREATE OR REPLACE RULE sum_op AS
>     ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
>   WHERE test.id = new.id;
>
> CREATE OR REPLACE RULE sum_op_up AS
>     ON UPDATE TO test DO  UPDATE test SET c = test.a + test.b
>   WHERE test.id = new.id;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Tue, Apr 25, 2006 at 02:27:23PM -0700, Bert wrote:
> I have a table construction like the one seen below, when i am updating
> or inserting i get a recurion, logical. But how to manage it that the
> rule is just doing it one time. Or is it possible to do the sum of a
> and b in an other way?
> ...
Bert,

(This is a resend to the list; I sent my reply privately by mistake).

Have you considered using a view to do the sums on the fly?  This
avoids all kinds of denormalization troubles (the sum can never be
incorrect):

wayne=# create table test (a int, b int);
CREATE TABLE
wayne=# create view test_sum as select *, a + b as c from test;
CREATE VIEW
wayne=# insert into test (a, b) values (1, 2);
INSERT 0 1
wayne=# insert into test (a, b) values (3, 4);
INSERT 0 1
wayne=# select * from test_sum;
 a | b | c
---+---+---
 1 | 2 | 3
 3 | 4 | 7
(2 rows)

Bert wrote:

>Hi list
>
>I have a table construction like the one seen below, when i am updating
>or inserting i get a recurion, logical. But how to manage it that the
>rule is just doing it one time. Or is it possible to do the sum of a
>and b in an other way?
>
>
Bert, i do this with triggers.  There are pros and cons.

One pro is that you can guarantee the correct result with code that
looks like this (I'm coding from memory, there may be some syntax errors):

if new.column_c <> old.column_c then
  raise error 'Cannot make direct assignment to calculated column
*column_c*';
end if;

Then you follow that up with the assignment, so that the code looks like:

if new.column_c <> old.column_c then
  raise error 'Cannot make direct assignment to calculated column
*column_c*';
end if;
new.column_c = new.column_a + new.column_b

The con is that these triggers go row-by-row.  Shockingly I have found
the degradation to be only 100% (instead of 700% or 1000%), so that
updates take twice as long.  In small-transaction situations this is not
a problem, it is lost in the overhead of the transaction itself.  On
large assigment statements that would take 2 minutes you now have to
wait 4 minutes, or break up the assignment.

The really cool thing about it is that you can  provide automation built
on top of normalized tables.  You get this by doing two things:

1) Derived values depend only upon normalized values or other derived values
2) never allow user writes to automated columns, raise an error when
that happens

Using views is fine for simple cases, but, and I know this because I've
done it, if you expect to automate calculations across 100's of tables
including complex and compound  calculations, your views will become
utterly unworkable, or destroy performance when 28 tables have to be
joined together when sombody issues "SELECT Total_exposure FROM Customers"

To really get the benefit, you can provide for a FETCH from parents to
children, and also SUMs from children to parent.  With that and the
simple extension of your example you can have really powerful normalized
and automated databases.

>CREATE TABLE test
>(
>  a int2,
>  b int2,
>  c int2,
>  id int2 NOT NULL,
>  CONSTRAINT id_test PRIMARY KEY (id)
>)
>WITHOUT OIDS;
>ALTER TABLE test OWNER TO postgres;
>
>
>CREATE OR REPLACE RULE sum_op AS
>    ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
>  WHERE test.id = new.id;
>
>CREATE OR REPLACE RULE sum_op_up AS
>    ON UPDATE TO test DO  UPDATE test SET c = test.a + test.b
>  WHERE test.id = new.id;
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>

Bert wrote:
> Hi list
>
> I have a table construction like the one seen below, when i am updating
> or inserting i get a recurion, logical. But how to manage it that the
> rule is just doing it one time. Or is it possible to do the sum of a
> and b in an other way?
>
> CREATE TABLE test
> (
>   a int2,
>   b int2,
>   c int2,
>   id int2 NOT NULL,
>   CONSTRAINT id_test PRIMARY KEY (id)
> )
> WITHOUT OIDS;

You do know you can write this like this?:
CREATE TABLE test
(
   a int2,
   b int2,
   c int2,
   id int2 NOT NULL PRIMARY KEY
)
WITHOUT OIDS;

> CREATE OR REPLACE RULE sum_op AS
>     ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
>   WHERE test.id = new.id;

How do you expect to update a record that doesn't exist yet?

I suppose what you meant is something like this (didn't check the
syntax, but the INSTEAD part is important):

CREATE OR REPLACE RULE sum_op AS
     ON INSERT TO TEST DO INSTEAD
     INSERT (a, b, c, id) VALUES (new.a, new.b, new.a + new.b, new.id);

But as others suggested, a view is probably the better way to go.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

Hi Kenneth
Thats exactly what i want, because we are running more selects than
inserts, and therefore a view is not the best way to go.
But I still have a problem, I was doing like you told me.
I have still the same table (without the rules definition)
So I created a trigger function:

CREATE FUNCTION trigger_test()
    RETURNS TRIGGER
    AS 'BEGIN
        new.c = (new.a + new.b);
        RETURN new;
    END;'
LANGUAGE 'plpgsql';

and then the Trigger:

CREATE TRIGGER trigger_sum
    AFTER INSERT OR UPDATE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_test();

The inserting and updating is doing well, but its not summing up the a
and b and save it to the c column. So far maybe you can help me second
time.
Thanks,
Bert

On April 26, 2006 02:35 pm, "Bert" <clemens.bertschler@gmail.com> wrote:
> CREATE TRIGGER trigger_sum
>     AFTER INSERT OR UPDATE
>     ON test
>     FOR EACH ROW
>     EXECUTE PROCEDURE trigger_test();
>
> The inserting and updating is doing well, but its not summing up the a
> and b and save it to the c column. So far maybe you can help me second
> time.

That would need to run BEFORE INSERT OR UPDATE, not AFTER.

--
Alan

Okay sorry i have to change the trigger from AFTER TO BEFORE
CREATE TRIGGER trigger_sum
        BEFORE INSERT OR UPDATE
        ON test
        FOR EACH ROW
        EXECUTE PROCEDURE trigger_test();

TO ALBAN
Our table definition are the same, so what? A primary key is a
constraint.
And
>> CREATE OR REPLACE RULE sum_op AS
>>     ON INSERT TO test DO  UPDATE test SET c = new.a + new.b
>>   WHERE test.id = new.id;

>How do you expect to update a record that doesn't exist yet?
This rule is creating first of all the insert and then i can run an
Update on this insert, so far its functioning. Your definition is quite
good but what are doing when you change the table definition? Rewrite
all Rules?
But nevertheless thank you.
Best regards,
Bert

Bert wrote:

>This rule is creating first of all the insert and then i can run an
>Update on this insert, so far its functioning. Your definition is quite
>good but what are doing when you change the table definition? Rewrite
>all Rules?
>
>
I actually use a data dictionary processor that automatically generates
the trigger code and also modifies table structures.  Your example would
look like this:

table example {
   column { col_A; col_B; }
   column col_C {
     chain calc {
         test { return: @col_A + @col_B; }
  }
}

This tool, called "Andromeda" is available for download if you like, but
we consider the current version "pre-Alpha".  The entire feature set is
defined and I use it for all of my projects, but we are still
stabilizing and documenting.  If you are interested in being a very
early adopter, drop me a line off-list.