Postgres Rules Preventing CTE Queries

Question

Using Postgres 9.3:

I am attempting to automatically populate a table when an insert is performed on another table. This seems like a good use for rules, but after adding the rule to the first table, I am no longer able to perform inserts into the second table using the writable CTE. Here is an example:

CREATE TABLE foo (
    id INT PRIMARY KEY
);

CREATE TABLE bar (
    id INT PRIMARY KEY REFERENCES foo
);

CREATE RULE insertFoo AS ON INSERT TO foo DO INSERT INTO bar VALUES (NEW.id);

WITH a AS (SELECT * FROM (VALUES (1), (2)) b)
INSERT INTO foo SELECT * FROM a

When this is run, I get the error

"ERROR: WITH cannot be used in a query that is rewritten by rules into multiple queries".

I have searched for that error string, but am only able to find links to the source code. I know that I can perform the above using row-level triggers instead, but it seems like I should be able to do this at the statement level. Why can I not use the writable CTE, when queries like this can (in this case) be easily re-written as:

INSERT INTO foo SELECT * FROM (VALUES (1), (2)) a

Does anyone know of another way that would accomplish what I am attempting to do other than 1) using rules, which prevents the use of "with" queries, or 2) using row-level triggers? Thanks,         

Answer 1

TL;DR: use triggers, not rules.

Generally speaking, prefer triggers over rules, unless rules are absolutely necessary. (Which, in practice, they never are.)

Using rules introduces heaps of problems which will needlessly complicate your life down the road. You've run into one here. Another (major) one is, for instance, that the number of affected rows will correspond to that of the very last query -- if you're relying on FOUND somewhere and your query is incorrectly reporting that no rows were affected by a query, you'll be in for painful bugs.

Moreover, there's occasional talk of deprecating Postgres rules outright:

http://postgresql.nabble.com/Deprecating-RULES-td5727689.html

Answer 2

As the other answer I definitely recommend using INSTEAD OF triggers before RULEs.

However if for some reason you don't want to change existing VIEW RULEs and still want use WITH you can do so by wrapping the VIEW in a stored procedure:

create function insert_foo(int) returns void as $$ 
  insert into foo values ($1) 
$$ language sql;

WITH a AS (SELECT * FROM (VALUES (1), (2)) b)
SELECT insert_foo(a.column1) from a;

This could be useful when using some legacy db through some system that wraps statements with CTEs.