SQL Rule

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:

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.

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.