Thread: problem with RULEs

problem with RULEs

From
Uros Gruber
Date:
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


Re: problem with RULEs

From
"Joel Burton"
Date:
> 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.


Re: problem with RULEs

From
Uros Gruber
Date:
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


Re: problem with RULEs

From
Masaru Sugawara
Date:
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



Re: problem with RULEs

From
Masaru Sugawara
Date:
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