Thread: problem with RULEs
Hi! I don't know if this can be done with RULES or i have to use FUNCTIONS. I have table categories with colums id,parent,name,cats this is some data 1 | 0 | cat1 | 3 2 | 0 | cat2 | 1 11 | 1 | cat3 | 1 12 | 1 | cat4 | 0 21 | 2 | cat5 | 0 111 | 11 | cat6 | 0 cats colum mean how mani categories are inside some category, U can see that cat1 have 3 sub cats, one is also sub ob category cat3. This column is like totalsub category. Now i want create rule on insert. When i create new category i want that this cats would get automaticaly increased in all required rows. For example if i insert category INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0) I would like to increase by one in cat3 and also in cat1. I've make some rule to increase oly parent category but where i try create new rule to update all subs i have error msg query rewritten 10 times, may contain cycles I think i've done something wrong. Can somebody help me with this. What RULE to apply or maybe this have to be done with function -- tia, Uros mailto:uros@sir-mag.com
> I don't know if this can be done with RULES or i have to use > FUNCTIONS. > > I have table categories with colums > > id,parent,name,cats > > this is some data > > 1 | 0 | cat1 | 3 > 2 | 0 | cat2 | 1 > 11 | 1 | cat3 | 1 > 12 | 1 | cat4 | 0 > 21 | 2 | cat5 | 0 > 111 | 11 | cat6 | 0 > > cats colum mean how mani categories are inside some category, > U can see that cat1 have 3 sub cats, one is also sub ob > category cat3. This column is like totalsub category. > > Now i want create rule on insert. When i create new category > i want that this cats would get automaticaly increased in all > required rows. > > For example if i insert category > > INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0) > > I would like to increase by one in cat3 and also in cat1. > > I've make some rule to increase oly parent category but where > i try create new rule to update all subs i have error msg > > query rewritten 10 times, may contain cycles > > I think i've done something wrong. > > Can somebody help me with this. What RULE to apply or maybe > this have to be done with function Uros -- You're getting the "may contain cycles" message because your UPDATE query goes to your rule, which issues an UPDATE query, which goes to your rule ... You could do this with a function w/o a rule, but you'd lose the natural solution that rules would provide... other users wouldn't have to understand anything to get the rule action to work. How about: Make a view of your table (CREATE VIEW xxx AS SELECT * FROM table) Instead of putting the INSERT/UPDATE rules on the table, put them on the view. Have the view INSERT and UPDATE rules calculate the correct values and perform the INSERTs and UPDATEs directly on the table. This way, your users can simply SELECT, INSERT, UPDATE, DELETE from the view and the changes will get made to the table -- without the risk of the recursive problem you're having now. J.
Hi! I think i don't understand this everything. How can i solve this with views. Can you put some example, maybe on my table. -- tia, Uros mailto:uros@sir-mag.com Friday, May 3, 2002, 7:36:28 PM, you wrote: >> I don't know if this can be done with RULES or i have to use >> FUNCTIONS. >> >> I have table categories with colums >> >> id,parent,name,cats >> >> this is some data >> >> 1 | 0 | cat1 | 3 >> 2 | 0 | cat2 | 1 >> 11 | 1 | cat3 | 1 >> 12 | 1 | cat4 | 0 >> 21 | 2 | cat5 | 0 >> 111 | 11 | cat6 | 0 >> >> cats colum mean how mani categories are inside some category, >> U can see that cat1 have 3 sub cats, one is also sub ob >> category cat3. This column is like totalsub category. >> >> Now i want create rule on insert. When i create new category >> i want that this cats would get automaticaly increased in all >> required rows. >> >> For example if i insert category >> >> INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0) >> >> I would like to increase by one in cat3 and also in cat1. >> >> I've make some rule to increase oly parent category but where >> i try create new rule to update all subs i have error msg >> >> query rewritten 10 times, may contain cycles >> >> I think i've done something wrong. >> >> Can somebody help me with this. What RULE to apply or maybe >> this have to be done with function JB> Uros -- JB> You're getting the "may contain cycles" message because your UPDATE query JB> goes to your rule, which issues an UPDATE query, which goes to your rule ... JB> You could do this with a function w/o a rule, but you'd lose the natural JB> solution that rules would provide... other users wouldn't have to understand JB> anything to get the rule action to work. JB> How about: JB> Make a view of your table (CREATE VIEW xxx AS SELECT * FROM table) JB> Instead of putting the INSERT/UPDATE rules on the table, put them on the JB> view. Have the view INSERT and UPDATE rules calculate the correct values and JB> perform the INSERTs and UPDATEs directly on the table. JB> This way, your users can simply SELECT, INSERT, UPDATE, DELETE from the view JB> and the changes will get made to the table -- without the risk of the JB> recursive problem you're having now. JB> J. JB> ---------------------------(end of broadcast)--------------------------- JB> TIP 6: Have you searched our list archives? JB> http://archives.postgresql.org
On Fri, 3 May 2002 21:27:55 +0200
Uros Gruber <uros@sir-mag.com> wrote:
> I think i don't understand this everything. How can i solve
> this with views. Can you put some example, maybe on my table.
>
> Friday, May 3, 2002, 7:36:28 PM, you wrote:
>
> >> I don't know if this can be done with RULES or i have to use
> >> FUNCTIONS.
In the recursive task, it seems to be considerably hard to accomplish
auto-increment function by using RULE. In stead of it, I would think its
task can be also solved by TRIGGER + recursive FUNCTIONs. The routines
to check the depth of child-to-parent relations and the value of cats
have been attached already. When necessary, you could take ones more
into account.
-- DROP TABLE categories;
CREATE TABLE categories(id int4 UNIQUE,
parent int4 NOT NULL,
name text,
cats int4 NOT NULL DEFAULT 0);
INSERT INTO categories VALUES( 1, 0, 'cat1', 3);
INSERT INTO categories VALUES( 2, 0, 'cat2', 1);
INSERT INTO categories VALUES( 11, 1, 'cat3', 1);
INSERT INTO categories VALUES( 12, 1, 'cat4', 0);
INSERT INTO categories VALUES( 21, 2, 'cat5', 0);
INSERT INTO categories VALUES(111, 11, 'cat6', 0);
-- DROP FUNCTION fn_inclement_cats(int4, int4);
CREATE OR REPLACE FUNCTION fn_inclement_cats(int4, int4) RETURNS boolean AS '
DECLARE
p int4; -- p is used for searching parent.
n int4; -- n is limitation of the depth of child-to-parent relations.
rec RECORD;
ret boolean := true;
BEGIN
p := $1;
n := $2;
WHILE ret = true LOOP
SELECT INTO rec * FROM categories WHERE id = p;
IF NOT FOUND THEN
ret := false;
ELSE
UPDATE categories SET cats = cats + 1 WHERE id = p;
RAISE NOTICE
''The value of cats at id = % is updated.'', rec.id;
IF n < 1000 THEN
ret := fn_inclement_cats(rec.parent, n + 1);
ELSE
RAISE EXCEPTION
''These child-to-parent relations are too deep !!'';
ret := false;
END IF;
END IF;
END LOOP;
RETURN ret;
END;
' LANGUAGE 'plpgsql';
-- DROP FUNCTION fn_cats();
CREATE OR REPLACE FUNCTION fn_cats() RETURNS opaque AS '
BEGIN
IF NEW.cats = 0 THEN
PERFORM fn_inclement_cats(NEW.parent, 1);
RAISE NOTICE ''Updating is done.'';
ELSE
RAISE EXCEPTION ''The value of cats must be zero.'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-- DROP TRIGGER tg_cats ON categories;
CREATE TRIGGER tg_cats
BEFORE INSERT ON categories
FOR EACH ROW
EXECUTE PROCEDURE fn_cats();
> >> For example if i insert category
> >>
> >> INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0)
> >>
> >> I would like to increase by one in cat3 and also in cat1.
> >>
renew=# select * from categories order by 1;
id | parent | name | cats
-----+--------+------+------
1 | 0 | cat1 | 3
2 | 0 | cat2 | 1
11 | 1 | cat3 | 1
12 | 1 | cat4 | 0
21 | 2 | cat5 | 0
111 | 11 | cat6 | 0
(6 rows)
renew=# insert into categories values(112, 11, 'cat7', 0);
NOTICE: The value of cats at id = 11 is updated.
NOTICE: The value of cats at id = 1 is updated.
NOTICE: Updating is done.
INSERT 74123 1
renew=# select * from categories order by 1;
id | parent | name | cats
-----+--------+------+------
1 | 0 | cat1 | 4
2 | 0 | cat2 | 1
11 | 1 | cat3 | 2
12 | 1 | cat4 | 0
21 | 2 | cat5 | 0
111 | 11 | cat6 | 0
112 | 11 | cat7 | 0
(7 rows)
Regards,
Masaru Sugawara
On Fri, 3 May 2002 21:27:55 +0200
Uros Gruber <uros@sir-mag.com> wrote:
> I think i don't understand this everything. How can i solve
> this with views. Can you put some example, maybe on my table.
>
> Friday, May 3, 2002, 7:36:28 PM, you wrote:
>
> >> I don't know if this can be done with RULES or i have to use
> >> FUNCTIONS.
In the recursive task, it seems to be considerably hard to accomplish
auto-increment function by using RULE. In stead of it, I would think its task
can be also solved by TRIGGER + recursive FUNCTIONs. The routines to check
the depth of child-to-parent relations and the value of cats have been attached
already. When necessary, you could take ones more into account.
-- DROP TABLE categories;
CREATE TABLE categories(id int4 UNIQUE,
parent int4 NOT NULL,
name text,
cats int4 NOT NULL DEFAULT 0);
INSERT INTO categories VALUES( 1, 0, 'cat1', 3);
INSERT INTO categories VALUES( 2, 0, 'cat2', 1);
INSERT INTO categories VALUES( 11, 1, 'cat3', 1);
INSERT INTO categories VALUES( 12, 1, 'cat4', 0);
INSERT INTO categories VALUES( 21, 2, 'cat5', 0);
INSERT INTO categories VALUES(111, 11, 'cat6', 0);
-- DROP FUNCTION fn_inclement_cats(int4, int4);
CREATE OR REPLACE FUNCTION fn_inclement_cats(int4, int4) RETURNS boolean AS '
DECLARE
p int4; -- p is used for searching parent.
n int4; -- n is limitation of the depth of child-to-parent relations.
rec RECORD;
ret boolean := true;
BEGIN
p := $1;
n := $2;
WHILE ret = true LOOP
SELECT INTO rec * FROM categories WHERE id = p;
IF NOT FOUND THEN
ret := false;
ELSE
UPDATE categories SET cats = cats + 1 WHERE id = p;
RAISE NOTICE
''The value of cats at id = % is updated.'', rec.id;
IF n < 1000 THEN
ret := fn_inclement_cats(rec.parent, n + 1);
ELSE
RAISE EXCEPTION
''These child-to-parent relations are too deep !!'';
ret := false;
END IF;
END IF;
END LOOP;
RETURN ret;
END;
' LANGUAGE 'plpgsql';
-- DROP FUNCTION fn_cats();
CREATE OR REPLACE FUNCTION fn_cats() RETURNS opaque AS '
BEGIN
IF NEW.cats = 0 THEN
PERFORM fn_inclement_cats(NEW.parent, 1);
RAISE NOTICE ''Updating is done.'';
ELSE
RAISE EXCEPTION ''The value of cats must be zero.'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-- DROP TRIGGER tg_cats ON categories;
CREATE TRIGGER tg_cats
BEFORE INSERT ON categories
FOR EACH ROW
EXECUTE PROCEDURE fn_cats();
> >> For example if i insert category
> >>
> >> INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0)
> >>
> >> I would like to increase by one in cat3 and also in cat1.
> >>
renew=# select * from categories order by 1;
id | parent | name | cats
-----+--------+------+------
1 | 0 | cat1 | 3
2 | 0 | cat2 | 1
11 | 1 | cat3 | 1
12 | 1 | cat4 | 0
21 | 2 | cat5 | 0
111 | 11 | cat6 | 0
(6 rows)
renew=# insert into categories values(112, 11, 'cat7', 0);
NOTICE: The value of cats at id = 11 is updated.
NOTICE: The value of cats at id = 1 is updated.
NOTICE: Updating is done.
INSERT 74123 1
renew=# select * from categories order by 1;
id | parent | name | cats
-----+--------+------+------
1 | 0 | cat1 | 4
2 | 0 | cat2 | 1
11 | 1 | cat3 | 2
12 | 1 | cat4 | 0
21 | 2 | cat5 | 0
111 | 11 | cat6 | 0
112 | 11 | cat7 | 0
(7 rows)
Regards,
Masaru Sugawara