Thread: Rule creation

Hello, I need help on this rule:

CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO
INSTEAD NOTHING;
Creation is ok but in inserting a row I get the following from pgaccessII
number -2147467259
ERROR: fireRIRrules: failed to remove aggs from qual...
Removing the INSTEAD from the rule inhibits the error but makes the rule
pointless as the statement execution proceeds to the insertion.
I need a rule that denies insertions if the total of the rows with an active
attribute maxes out (guess what, a vacancy set).
I'm confused, any help?


--  9:33pm  up  1:00,  1 user,  load average: 0.87, 0.47, 0.21

Edoardo Causarano <curious.dot.corn.at.katamail.dot.com@postgresql.org> writes:
> Hello, I need help on this rule:
> CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO
> INSTEAD NOTHING;
> Creation is ok but in inserting a row I get the following from pgaccessII
> number -2147467259
> ERROR: fireRIRrules: failed to remove aggs from qual...

This is a known bug, but the fix will be to reject such rules entirely.
An aggregate in a rule's WHERE clause is not sensible --- what are you
aggregating over?

> I need a rule that denies insertions if the total of the rows with an active 
> attribute maxes out (guess what, a vacancy set).

I think a rule is the wrong way to approach this anyhow.  What would
probably make more sense is an AFTER INSERT OR UPDATE trigger that
runs a SUM() computation on the table and throws an error if it doesn't
like the result.
        regards, tom lane