Re: INSERT INTO ... SELECT problem

Alex Perel <veers(at)webhosting(dot)com> writes:
> CREATE RULE ip_allocated_rule AS
> ON INSERT
> TO ips_used
> DO DELETE FROM ips_free
> WHERE ips_free.block_id = NEW.block_id
> AND ips_free.ip = NEW.ip;

> INSERT INTO ips_used
> (
> block_id,
> ip,
> contact_id
> )
> SELECT block_id
> , ip
> , '1000'
> FROM ips_free
> WHERE ip = '10.10.10.10'

Hmm. The rule will generate a query along these lines:

DELETE FROM ips_free
FROM ips_free ipsfree2
WHERE ips_free.block_id = ipsfree2.block_id
AND ips_free.ip = ipsfree2.ip
AND ipsfree2.ip = '10.10.10.10';

(I'm using ipsfree2 to convey the idea of a self-join similar to
"SELECT FROM ips_free, ips_free ipsfree2" ... I don't believe the
above is actually legal syntax for DELETE.)

This ends up deleting all your ips_free entries for ip = '10.10.10.10',
which seems to be what you want ... but I think the query added by
the rule is executed before the actual INSERT, which leaves you with
nothing to insert.

There's been some debate in the past about whether an ON INSERT rule
should fire before or after the INSERT itself. I lean to the "after"
camp myself, which would fix this problem for you. However, you are
treading right on the hairy edge of circular logic here. You might want
to think about using a trigger rather than a rule to do the deletes.

regards, tom lane