Re: problem with RULEs

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(at)sir-mag(dot)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