Re: [GENERAL] SQL Rule
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.
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL Rule
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL Rule
On April 26, 2006 02:35 pm, "Bert" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL Rule
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SQL Rule
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 [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL Rule
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 begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL Rule
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) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 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: 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] 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; ---(end of broadcast)--- TIP 6: explain analyze is your friend
