Re: Writeable CTEs and side effects

On Thu, 2009-10-08 at 12:34 -0700, Jeff Davis wrote:
> I'm still trying to ponder the consequences of this. Most people
> assume
> that a single statement means that everything in the statement happens
> at once (intuitively). The few cases where that's not true are special
> commands or things that we are trying to fix, like:
> "UPDATE foo SET a = a + 1".

Well, the classical case of

INSERT INTO tab1 SELECT ... FROM tab1

clearly requires the SELECT to be distinctly before the INSERT.

Basically, this would not do it the other way around: write first, then
select.

I'm not sure why it needs to be tied in with CTEs, though. Why couldn't
this work:

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".