Обсуждение: Confusing reference to MERGE use inside WITH
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/queries-with.html Description: Hi. In the queries WITH documentation https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing that MERGE is referenced in several places as being something you can use inside with, even though it can't. If you search for MERGE on the page, you will find the 2 first results is a reference saying MERGE can be used inside WITH, the same as SELECT, INSERT, UPDATE, or DELETE. The 3rd result then correctly states that MERGE cannot be used inside WITH. If I'm misunderstanding the meaning of the first 2 results, then I apologise :) Greetings Rasmus Porsager
On Sun, Jul 16, 2023 at 8:48 PM PG Doc comments form <noreply@postgresql.org> wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/queries-with.html > Description: > > Hi. > > In the queries WITH documentation > https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing > that MERGE is referenced in several places as being something you can use > inside with, even though it can't. > > If you search for MERGE on the page, you will find the 2 first results is a > reference saying MERGE can be used inside WITH, the same as SELECT, INSERT, > UPDATE, or DELETE. The 3rd result then correctly states that MERGE cannot be > used inside WITH. > > If I'm misunderstanding the meaning of the first 2 results, then I apologise > :) > > Greetings > Rasmus Porsager I agree. The second one is kind of confusing. > WITH w AS ( > SELECT key, very_expensive_function(val) as f FROM some_table > ) > SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f; > Here, materialization of the WITH query ensures that very_expensive_function is evaluated only once per table row, nottwice. > The examples above only show WITH being used with SELECT, but it can be attached in the same way to INSERT, UPDATE, DELETE,> or MERGE. There are two "SELECT". " only show WITH being used with SELECT", not mention of "SELECT" refer to the auxiliary statements or primary statements. -- I recommend David Deutsch's <<The Beginning of Infinity>> Jian
On Sun, Jul 16, 2023 at 5:48 AM PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/queries-with.html
Description:
Hi.
In the queries WITH documentation
https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing
that MERGE is referenced in several places as being something you can use
inside with, even though it can't.
If you search for MERGE on the page, you will find the 2 first results is a
reference saying MERGE can be used inside WITH,
the same as SELECT, INSERT,
UPDATE, or DELETE.
No, they are not, see below.
The 3rd result then correctly states that MERGE cannot be
used inside WITH.
Yes.
The first non-intro section says that the following syntax is allowed (you can attach WITH /TO/ MERGE)
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
The second section is saying that the "with_query" above cannot contain MERGE, i.e., you cannot put MERGE /IN/ WITH
Namely because you cannot attach a RETURNING clause to MERGE.
The intro makes it quite clear as well:
Specifically, reading the last sentence makes it clear:
Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.
Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.
Here the inside is called the auxiliary while the outside is called primary.
David J.