Understanding With query operation

Question

I saw this example in postgresql guide:

WITH t AS (
DELETE FROM foo
) 
DELETE FROM bar;

The manual says:

This example would remove all rows from tables foo and bar. The number of affected rows reported to the client would only include rows removed from bar.

Why would it delete from foo if I didn't "call" t?

Normaly with is used like:

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

There is an actuall call to t which only then "summons" the with block.

Can someone explain this?

Related: http://stackoverflow.com/a/15810159/939860 – Erwin Brandstetter Aug 23 '15 at 22:10 — Erwin Brandstetter, Aug 23 '15 at 22:10

klin · Accepted Answer · 2015-08-23T11:05:53.180

2

The quoted example shows that a query in with clause is executed regardless of whether it is used in the main query or not. With this principle you can perform several independent operations in a single query. It is a very convenient solution and greatly expands the flexibility of SQL.

Note that if you do not want the query t to be executed, just do not use it:

 DELETE FROM bar;

If you have used

 WITH t AS (
 DELETE FROM foo
 )
 DELETE FROM bar;

this means that your intention is to remove rows from both tables foo and bar in one query.

In the documentation you can find:

If a data-modifying statement in WITH lacks a RETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless.

edited Aug 23 '15 at 11:05

answered Aug 23 '15 at 10:51

klin

112,967
15
204
232

It would be helpful if there were quotes from the specification about this behaviour of `WITH` (since at the moment it's not obvious what this answer is based on). – zerkms Aug 23 '15 at 11:00
@zerkms: The explanation in the documentation is adjacent to the cited example ... – klin Aug 23 '15 at 11:07
Now it's *the* answer :-) – zerkms Aug 23 '15 at 11:09
So if I write just `WITH t AS ( DELETE FROM foo )` nothing will happen? correct? – Un Peu Aug 23 '15 at 12:05
1

@UnPeu: Nothing will happen because you'll have a syntax error. – klin Aug 23 '15 at 12:16

Understanding With query operation

1 Answers1