Re: Rules: A Modest Proposal

On Mon, Oct 05, 2009 at 10:32:53AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > WITH NEW AS (
> > insert into t values (floor(random()*1000)::integer);
> > RETURNING *
> > )
> > insert into t_log values (NEW.a);
>
> > Would this not have the required semantics?
>
> Interesting idea, but it's not clear how to make it work with multiple
> DO ALSO rules, nor with conditional DO INSTEAD rules.

Well, my (possibly naive) view is:

- Multiple DO ALSO rules seem easy. There is a patch in the works which
makes INSERT/UPDATE/DELETE into proper node types so they can
actually appear in the WITH clause above. With a minor extension you
could create a MultipleStatement node type which merely runs each
substatement, like Append, but for plans.

- Conditional DO INSTEAD rules are brain benders. Logically, I think
they split the plan in two, one with the condition, one with the
negative of the condition. So *maybe* they could also be handled by
such a MultipleStatement node but then...

I get visions of people writing a SELECT rule with a conditional DELETE
statement with RETURNING *. Then, SELECTing the table would return
everything but conditionally DELETE some rows. Something like:

WITH OLD AS (SELECT * FROM foo)
MULTISTATEMENT(
SELECT * FROM OLD WHERE condition;
DELETE FROM OLD WHERE NOT condition RETURNING *;
)

As for actual implementation it seems doable, but I may be being
impossibly naive.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.