Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 3AA80477461; Fri,  4 Oct 2002 10:52:47 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP
	id C13B847714B; Fri,  4 Oct 2002 10:24:43 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g94EOmhR020528;
	Fri, 4 Oct 2002 10:24:48 -0400 (EDT)
To: Edoardo Causarano <curious.dot.corn.at.katamail.dot.com@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule creation 
In-reply-to: <200210032134.24682.Edoardo Causarano <curious dot corn at
	katamail dot com>>
References: <200210032134.24682.Edoardo Causarano <curious dot corn at
	katamail dot com>>
Comments: In-reply-to Edoardo Causarano
	<curious.dot.corn.at.katamail.dot.com@postgresql.org>
	message dated "Thu, 03 Oct 2002 21:34:24 +0200"
Date: Fri, 04 Oct 2002 10:24:47 -0400
Message-ID: <20527.1033741487@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/63
X-Sequence-Number: 9781

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