Re: Rule creation

Edoardo Causarano <curious(dot)dot(dot)corn(dot)at(dot)katamail(dot)dot(dot)com(at)postgresql(dot)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