Re: Rule appears not to fire on insert w/ "except"

On Mon, Nov 21, 2005 at 08:05:19PM -0500, Jerry Sievers wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
>
> > Hello All,
> >
> > We have finally tracked down a bug in our application to a rewrite rule on a
> > table.  In essence, the rewrite rule in question logs any inserts to another
> > table.  This works correctly in all cases except where an "except" clause is
> > used in the insert statement.  In this case, the rows are inserted into the
> > primary table as expected, but the rule either does not fire, or fires in
> > such a way that nothing is placed in the changes table.
>
> You must be referring to something like;
>
> insert into foo
> select *
> from sometable
> except
> select *
> from someothertable
> ;
>
> If there's an EXCEPT clause on INSERT, I've never seen it.

I suppose you could wrap the SELECT...EXCEPT in parens.

WARNING Untested Code:

INSERT INTO foo
(SELECT a,b,c FROM bar
EXCEPT
SELECT a,b,c FROM baz);

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!