CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
Un Peu
Un Peu

Reputation: 131

Understanding With query operation

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?

Upvotes: 1

Views: 69

Answers (1)

klin
klin

Reputation: 121919

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.

Upvotes: 2

Related Questions