Re: Writeable CTEs and side effects

On Fri, 2009-10-09 at 02:23 +0300, Peter Eisentraut wrote:
> INSERT INTO tab1 SELECT ... FROM tab1
> 
> clearly requires the SELECT to be distinctly before the INSERT.

That's effectively only one thing: assigning a relation (the result of
the select) to a variable (tab1). I was talking about multiple
assignment.

What if you want to append foo to bar and bar to foo?

WITH t1 AS (INSERT INTO foo SELECT * FROM bar), t2 AS (INSERT INTO bar SELECT * FROM foo)
VALUES(1);

That could be an interesting command if we didn't increment the command
counter.

> SELECT * FROM test1 WHERE a IN (UPDATE test2 SET b = b + 1 RETURNING b);
> 
> I think I'd want "writable subqueries" instead of only "writable CTEs".

I think the original motivation was that it's more clear that a CTE is
separated and can only be executed once (if it has side effects).
Depending on how the query is written, it might be less obvious how many
times the subquery should be executed, and it might change based on the
plan.

We could make the same rules for a subquery that has side effects, and
always materialize it. But for now maybe CTEs are a better place to get
the feature working.

Regards,Jeff Davis