pg.ddx.io pgsql-docs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Confusing reference to MERGE use inside WITH 3+ messages / 3 participants [nested] [flat]
* Confusing reference to MERGE use inside WITH @ 2023-07-15 14:37 PG Doc comments form <noreply@postgresql.org> 2023-07-17 01:16 ` Re: Confusing reference to MERGE use inside WITH jian he <jian.universality@gmail.com> 2023-07-17 01:48 ` Re: Confusing reference to MERGE use inside WITH David G. Johnston <david.g.johnston@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: PG Doc comments form @ 2023-07-15 14:37 UTC (permalink / raw) To: pgsql-docs@lists.postgresql.org; +Cc: rasmus@porsager.com 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Confusing reference to MERGE use inside WITH 2023-07-15 14:37 Confusing reference to MERGE use inside WITH PG Doc comments form <noreply@postgresql.org> @ 2023-07-17 01:16 ` jian he <jian.universality@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: jian he @ 2023-07-17 01:16 UTC (permalink / raw) To: rasmus@porsager.com; pgsql-docs@lists.postgresql.org 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, not twice. > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Confusing reference to MERGE use inside WITH 2023-07-15 14:37 Confusing reference to MERGE use inside WITH PG Doc comments form <noreply@postgresql.org> @ 2023-07-17 01:48 ` David G. Johnston <david.g.johnston@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: David G. Johnston @ 2023-07-17 01:48 UTC (permalink / raw) To: rasmus@porsager.com; pgsql-docs@lists.postgresql.org 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 ] https://www.postgresql.org/docs/current/sql-merge.html 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. Here the inside is called the auxiliary while the outside is called primary. David J. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-07-17 01:48 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-07-15 14:37 Confusing reference to MERGE use inside WITH PG Doc comments form <noreply@postgresql.org> 2023-07-17 01:16 ` jian he <jian.universality@gmail.com> 2023-07-17 01:48 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox