Re: problem with RULEs

On Fri, 3 May 2002 21:27:55 +0200
Uros Gruber <uros(at)sir-mag(dot)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