proberm with rule

I have two tables. One is test_main, and second is named result. Also I have
view which summarizes results from test_main table and groups them by ID
column:

************************
CREATE TABLE test_main(id varchar(4), value int4);

CREATE TABLE result(id varchar(4), value int4);

CREATE VIEW summing AS
SELECT test_main.id, sum(test_main.value) AS suma
GROUP BY test_main.id;
**************************

And here is my problem: I want to make a rule which will insert new row in
table result which will have ID and SUM value of that ID.
*************************
CREATE RULE tester AS
ON INSERT TO test_main

DO INSERT INTO "result" (id, value)
VALUES (new.id, summing.suma);
*************************
So, when this rule is executed, for this INSERT commands:
*************************
insert into test_main values('0003', 100)

insert into test_main values('0004', 100)
*************************
I get in column result:
*************************
0003, 100
0004, 100
0004, 100
**************************
and I need
*********************
0003, 100
0004, 100

Can anybody help me in doing this?
Thanks in advance

Re: proberm with rule

"Sreten Milosavljevic" wrote in message news:...
***SNIP***

Instead of just asking someone to write the whole thing
why not give it a try and then post code that is not
working or code that your confused about. That being
said...

You didn't mention what version of PostgreSQL you were
using so I'll assume a 7.4.x version.

First of all this is a two step process;

1. Create your function with return type TRIGGER.
2. Create a trigger on your table using the function you just defined.

-- Create function to execute our insert.
CREATE OR REPLACE FUNCTION Tester() RETURNS TRIGGER
AS '
BEGIN
-- The object NEW contains information just inserted into test_main.
INSERT INTO result (id, value) VALUES(NEW.id, NEW.value);
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

-- Create the trigger on the table test_main.
CREATE TRIGGER tester
AFTER INSERT
ON test_main
FOR EACH ROW
EXECUTE PROCEDURE Tester();


Not sure if that's what you wanted. I just wrote that on the fly so
it might contain syntax errors or for that matter I might have forgotten
something. At least that should get you started.

Cheers!