Re: problem with RULEs - Mailing list pgsql-general

From Uros Gruber
Subject Re: problem with RULEs
Date
Msg-id 1039634781.20020503212755@sir-mag.com
Whole thread Raw
In response to Re: problem with RULEs  ("Joel Burton" <joel@joelburton.com>)
Responses Re: problem with RULEs
Re: problem with RULEs
List pgsql-general
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


pgsql-general by date:

Previous
From: "Marc G. Fournier"
Date:
Subject: Re: Fwd: Postfix Relay Hub SMTP server: errors from
Next
From: Tom Lane
Date:
Subject: Re: Subject: bool / vacuum full bug followup part 2