Re: problem with RULEs - Mailing list pgsql-general

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