"writable CTEs"
As a side note, I think the term "writable CTE" is a misnomer. The CTE
is not writable. The CTE is the result of a write operation.
A writable CTE would look like this:
WITH foo AS (SELECT ...) UPDATE foo SET ...
a bit like an updatable view.
AFAICT, the current patch doesn't use the term, so there is no problem,
but just for those who are preparing propaganda and such.
On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
As a side note, I think the term "writable CTE" is a misnomer. The CTE
is not writable. The CTE is the result of a write operation.A writable CTE would look like this:
WITH foo AS (SELECT ...) UPDATE foo SET ...
a bit like an updatable view.
AFAICT, the current patch doesn't use the term, so there is no problem,
but just for those who are preparing propaganda and such.
I think I've used "DML WITH" in the patch, but I don't like that either.
Naming this feature seems to be quite a challenge.
I'd prefer something short but easily understandable, but those two
might be mutually exclusive.
Regards,
Marko Tiikkaja
On Wed, Dec 22, 2010 at 10:44 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
I'd prefer something short but easily understandable, but those two might be
mutually exclusive.
Volatile CTE's doesn't add any more clarity either. Maybe "Round Trip
Reduction" CTE's. :)
--
Regards,
Richard Broersma Jr.
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
I think I've used "DML WITH" in the patch, but I don't like that
either. Naming this feature seems to be quite a challenge.I'd prefer something short but easily understandable, but those
two might be mutually exclusive.
How about?:
DML CTEs
DML-based CTEs
RETURNING-based CTEs
-Kevin
On Wed, Dec 22, 2010 at 12:54:39PM -0600, Kevin Grittner wrote:
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
I think I've used "DML WITH" in the patch, but I don't like that
either. Naming this feature seems to be quite a challenge.I'd prefer something short but easily understandable, but those
two might be mutually exclusive.How about?:
DML CTEs
DML-based CTEs
RETURNING-based CTEs
When I designed the feature, I'd planned to be able to put most kinds
of statement inside or outside the CTE, not just DML writes. You can
imagine cases for DCL (GRANT/REVOKE based on a catalog query) or DDL
(partition management), and I did.
We could call them, "Expanded CTEs," but that only freezes the prior
norm making them read-only, so I think "Writeable CTEs" captures it
pretty well.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On ons, 2010-12-22 at 20:44 +0200, Marko Tiikkaja wrote:
On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
As a side note, I think the term "writable CTE" is a misnomer. The CTE
is not writable. The CTE is the result of a write operation.A writable CTE would look like this:
WITH foo AS (SELECT ...) UPDATE foo SET ...
a bit like an updatable view.
AFAICT, the current patch doesn't use the term, so there is no problem,
but just for those who are preparing propaganda and such.I think I've used "DML WITH" in the patch, but I don't like that either.
Naming this feature seems to be quite a challenge.
*Writing* CTEs is more accurate.
On Mon, Dec 27, 2010 at 11:47:14PM +0200, Peter Eisentraut wrote:
On ons, 2010-12-22 at 20:44 +0200, Marko Tiikkaja wrote:
On 2010-12-22 8:28 PM, Peter Eisentraut wrote:
As a side note, I think the term "writable CTE" is a misnomer. The CTE
is not writable. The CTE is the result of a write operation.A writable CTE would look like this:
WITH foo AS (SELECT ...) UPDATE foo SET ...
a bit like an updatable view.
AFAICT, the current patch doesn't use the term, so there is no problem,
but just for those who are preparing propaganda and such.I think I've used "DML WITH" in the patch, but I don't like that either.
Naming this feature seems to be quite a challenge.*Writing* CTEs is more accurate.
OK :)
On the bright side, we have a decades-long tradition of horrible names
on this project, one early example of which is a name that abbreviates
the phrase, 'POST-"interactive GRaphics REtrieval System."' ;)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.
Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs at
certain select places? I could write a documentation patch.
--
Regards,
Peter Geoghegan
Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think
that that's a mistake because we call them CTEs everywhere else.Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs
at certain select places? I could write a documentation patch.
Personally, I think it's worth fixing. This sort of disjunction
between code and documentation can cause confusing for someone
trying to get started on hacking. It is an exception to the
otherwise excellent documentation of both the product and the code.
-Kevin
Import Notes
Resolved by subject fallback
On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.
Agreed.
Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs
at certain select places? I could write a documentation patch.
+1 :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Dec 27, 2010 at 9:28 PM, David Fetter <david@fetter.org> wrote:
On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.Agreed.
Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs
at certain select places? I could write a documentation patch.+1 :)
Would it make sense to work a section into the documentation
explaining the semantics of CTEs? e.g., evaluate-once.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Dec 27, 2010 at 09:51:01PM -0500, Robert Haas wrote:
On Mon, Dec 27, 2010 at 9:28 PM, David Fetter <david@fetter.org> wrote:
On Tue, Dec 28, 2010 at 12:19:47AM +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.Agreed.
Is there interest in correcting this, by putting "CTEs" or "Common
table expressions" in parenthesis after "WITH queries" in the docs
at certain select places? I could write a documentation patch.+1 :)
Would it make sense to work a section into the documentation
explaining the semantics of CTEs? e.g., evaluate-once.
Yep. It should probably go where "WITH Queries" is now, i.e.
http://www.postgresql.org/docs/current/static/queries-with.html
would become (doesn't exist yet):
http://www.postgresql.org/docs/current/static/queries-common-table-expression.html
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On 28 December 2010 01:09, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Personally, I think it's worth fixing. This sort of disjunction
between code and documentation can cause confusing for someone
trying to get started on hacking. It is an exception to the
otherwise excellent documentation of both the product and the code.
Hmm. Having looked at the relevant sgml file, queries.sgml, common
table expressions appear at one point:
<indexterm>
<primary>common table expression</primary>
<see>WITH</see>
</indexterm>
This indicates that the term common table expression should be indexed
(the dead tree way), which isn't much use for the majority of users
that access the docs on the web. This term doesn't appear in the html
source. Perhaps whatever infrastructure we use to render the sgml
files as html for dot org should produce keyword meta tags for indexed
terms, in case anyone searches the docs using Altavista. More
seriously, if we did this I imagine we'd see WITH Queries (for
example) in the first page of results if we search for "common table
expression" from dot org directly. The fact that whatever docbook tool
we use doesn't already do this does suggests that it might not be such
a good idea. It may not be worth the effort. I've cc'd Thom Brown to
see what he thinks.
Attached documentation patch should make things clearer. I haven't
changed the "queries-with" section to
"queries-common-table-expression" per David's suggestion for the sake
of stability. I hesitate to change it without reaching a consensus -
will this break a lot of links?
The main change I've made is: "WITH queries, also referred to as
Common table expressions or CTEs, provide a way to write subqueries
for use as part of a larger query". I'm concerned that this might not
be strictly correct, because the term "WITH query" may not be exactly
equivalent to the term "CTE" - WITH queries are comprised of one or
more CTEs, plus a main query. Or are they?
Comments?
--
Regards,
Peter Geoghegan
Attachments:
cte_doc.patchtext/x-patch; charset=US-ASCII; name=cte_doc.patchDownload
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index f6e081e..45df052 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1525,7 +1525,7 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<sect1 id="queries-with">
- <title><literal>WITH</literal> Queries</title>
+ <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
@@ -1538,10 +1538,10 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
</indexterm>
<para>
- <literal>WITH</> provides a way to write subqueries for use in a larger
- query. The subqueries can be thought of as defining
- temporary tables that exist just for this query. One use of this feature
- is to break down complicated queries into simpler parts. An example is:
+ <literal>WITH</> queries, also referred to as Common table expressions or
+ <acronym>CTE</acronym>s, provide a way to write subqueries for use as part of a larger query.
+ The subqueries can be thought of as defining temporary tables that exist just for this query.
+ One use of this feature is to break down complicated queries into simpler parts. An example is:
<programlisting>
WITH regional_sales AS (
On Tue, Dec 28, 2010 at 03:49:16AM +0000, Peter Geoghegan wrote:
On 28 December 2010 01:09, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Personally, I think it's worth fixing. �This sort of disjunction
between code and documentation can cause confusing for someone
trying to get started on hacking. �It is an exception to the
otherwise excellent documentation of both the product and the code.Hmm. Having looked at the relevant sgml file, queries.sgml, common
table expressions appear at one point:<indexterm>
<primary>common table expression</primary>
<see>WITH</see>
</indexterm>This indicates that the term common table expression should be
indexed (the dead tree way), which isn't much use for the majority
of users that access the docs on the web. This term doesn't appear
in the html source. Perhaps whatever infrastructure we use to render
the sgml files as html for dot org should produce keyword meta tags
for indexed terms, in case anyone searches the docs using Altavista.
More seriously, if we did this I imagine we'd see WITH Queries (for
example) in the first page of results if we search for "common table
expression" from dot org directly. The fact that whatever docbook
tool we use doesn't already do this does suggests that it might not
be such a good idea. It may not be worth the effort. I've cc'd Thom
Brown to see what he thinks.Attached documentation patch should make things clearer. I haven't
changed the "queries-with" section to
"queries-common-table-expression" per David's suggestion for the
sake of stability. I hesitate to change it without reaching a
consensus - will this break a lot of links?
I don't see how people can be relying on links to 9.1-to-be's
documentation.
The main change I've made is: "WITH queries, also referred to as
Common table expressions or CTEs, provide a way to write subqueries
for use as part of a larger query". I'm concerned that this might
not be strictly correct, because the term "WITH query" may not be
exactly equivalent to the term "CTE" - WITH queries are comprised of
one or more CTEs, plus a main query. Or are they?
They are. :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Dec 28, 2010 at 12:45 AM, David Fetter <david@fetter.org> wrote:
I don't see how people can be relying on links to 9.1-to-be's
documentation.
Well, it's always handy when the filenames are the same across
versions. Ever looked at the 9.0 documentation for something and then
modified the URL to see what it looked like in 8.1 or something?
The main change I've made is: "WITH queries, also referred to as
Common table expressions or CTEs, provide a way to write subqueries
for use as part of a larger query". I'm concerned that this might
not be strictly correct, because the term "WITH query" may not be
exactly equivalent to the term "CTE" - WITH queries are comprised of
one or more CTEs, plus a main query. Or are they?They are. :)
No, actually I think Peter has it right. A query with one or more
common table expressions is a WITH-query. This is a subtle difference
but could affect the way that things are phrased in the documentation.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Excerpts from David Fetter's message of mar dic 28 02:45:11 -0300 2010:
On Tue, Dec 28, 2010 at 03:49:16AM +0000, Peter Geoghegan wrote:
Attached documentation patch should make things clearer. I haven't
changed the "queries-with" section to
"queries-common-table-expression" per David's suggestion for the
sake of stability. I hesitate to change it without reaching a
consensus - will this break a lot of links?I don't see how people can be relying on links to 9.1-to-be's
documentation.
If you change it to give it a new name, the old name disappears.
It's a little thing but we've gotten complaints about links disappearing
from one version to another. (Personally this has given me reason to
think that the "/current" link to docs are a bad idea).
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 28 December 2010 12:09, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Dec 28, 2010 at 12:45 AM, David Fetter <david@fetter.org> wrote:
I don't see how people can be relying on links to 9.1-to-be's
documentation.Well, it's always handy when the filenames are the same across
versions. Ever looked at the 9.0 documentation for something and then
modified the URL to see what it looked like in 8.1 or something?
I do this all the time. Anyway, I intend for this doc patch to be
backported to 8.4 as a bugfix, which is part of the reason why it
isn't invasive - it's just a clarification. Clearly if it makes sense
for 9.1, it makes just as much sense for 9.0 and 8.4.
No, actually I think Peter has it right. A query with one or more
common table expressions is a WITH-query. This is a subtle difference
but could affect the way that things are phrased in the documentation.
Attached is a new patch written with this consideration in mind. It
also has an acronym.sgml entry for CTE, which was absent from my
earlier patch. I think David actually agreed that I was right to have
doubts.
--
Regards,
Peter Geoghegan
Attachments:
cte_doc.patchtext/x-patch; charset=US-ASCII; name=cte_doc.patchDownload
diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml
index 23ab3b4..5dad0db 100644
--- a/doc/src/sgml/acronyms.sgml
+++ b/doc/src/sgml/acronyms.sgml
@@ -99,6 +99,15 @@
</varlistentry>
<varlistentry>
+ <term><acronym>CTE</acronym></term>
+ <listitem>
+ <para>
+ <link linkend="queries-with">Common Table Expression</link>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><acronym>CVE</acronym></term>
<listitem>
<para>
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index f6e081e..97e3977 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1525,7 +1525,7 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<sect1 id="queries-with">
- <title><literal>WITH</literal> Queries</title>
+ <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
@@ -1539,7 +1539,8 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<para>
<literal>WITH</> provides a way to write subqueries for use in a larger
- query. The subqueries can be thought of as defining
+ query. The subqueries, which are often referred to as Common table
+ expressions or <acronym>CTE</acronym>s, can be thought of as defining
temporary tables that exist just for this query. One use of this feature
is to break down complicated queries into simpler parts. An example is:
On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.
I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.
Peter Eisentraut <peter_e@gmx.net> writes:
On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.
I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.
I'm with Peter on that. CTE is a completely meaningless term to most
users.
As for the problem at hand, couldn't we use "WITH ... RETURNING", or
some other phrase based on what users actually see/write? DML has
the same problem as CTE, namely it's just another damn TLA. It may
be one that more people have heard of, but that doesn't make it
particularly attractive.
regards, tom lane
On 28 December 2010 14:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm with Peter on that. CTE is a completely meaningless term to most
users.
I don't believe that that's the case. If CTE is a completely
meaningless term to most users, WITH query is even more meaningless. I
never refer to WITH queries in conversation, and I have never heard
someone else do so. I have often talked about CTEs though. Besides,
I'm not suggesting that we should completely change the title, or
change the section name at all, or change any existing text from the
docs. The doc patch is just a clarification that I believe will be
useful.
If I search for "common table expressions" on Wikipedia, I am sent to
the common table expressions article, without any re-direction. The
article doesn't mention "with query" as a synonym of CTE at any point.
If I search for "With query", the first page of results (20 articles)
doesn't have anything about CTEs at all. The situation with Google is
similar. The situation with postgresql.org is similar, except that
searching for CTE there is fairly useless too. Granted, all of this
may have something to do with the ambiguity of the term "with query"
in a more general context, but the fact that I never hear the term in
conversation probably has something to do with that too.
--
Regards,
Peter Geoghegan
On Tue, Dec 28, 2010 at 04:35:26PM +0200, Peter Eisentraut wrote:
On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.
Common Table Expression, or CTE for short, is the standard
terminology, and I don't just mean SQL:2008. It's standard in DB2,
Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and
Sybase SQL Anywhere, at a minimum.
"WITH query" is a pure PostgreSQL invention, and not a super helpful
one to the vast majority of users. Please bear in mind that if this
is to remain a successful project, the vast majority of users are
*future* users, not current or past ones. We don't talk about
"Subquery queries" or "FULL JOIN queries" and give them their own doc
section, nor should we. We should instead refactor the docs to point
to CTEs in the appropriate places, and it's my hope that those places
will increase over time.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On tis, 2010-12-28 at 16:04 +0000, Peter Geoghegan wrote:
If I search for "common table expressions" on Wikipedia, I am sent to
the common table expressions article, without any re-direction. The
article doesn't mention "with query" as a synonym of CTE at any point.
If I search for "With query", the first page of results (20 articles)
doesn't have anything about CTEs at all.
Apparently, the term "common table expression" comes from Microsoft and
IBM. If you search for "SELECT WITH clause" you get a bunch of Oracle
links.
On tis, 2010-12-28 at 09:31 -0800, David Fetter wrote:
Common Table Expression, or CTE for short, is the standard
terminology, and I don't just mean SQL:2008. It's standard in DB2,
Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and
Sybase SQL Anywhere, at a minimum."WITH query" is a pure PostgreSQL invention, and not a super helpful
one to the vast majority of users.
The phrase "common table expression" does not appear anywhere in the SQL
standard. The standard uses the grammar symbol <with clause>.
On 28 December 2010 20:07, Peter Eisentraut <peter_e@gmx.net> wrote:
The phrase "common table expression" does not appear anywhere in the SQL
standard. The standard uses the grammar symbol <with clause>.
I think we're losing sight of the issue a bit here.
No one is proposing that we call WITH queries common table
expressions. As I think we all agree, the term "WITH query" and
"common table expression" are not synonymous. A WITH query is
comprised of one or more common table expressions, plus a conventional
SELECT query.
All that I'm asking is that we /specify/ that the "subqueries" already
mentioned in the docs are common table expressions. This terminology
is less confusing and ambiguous, is demonstrably already in widespread
use, and will probably lay the groundwork for whatever name we choose
for wCTEs.
I think that it makes sense to change the title of the relevant
section from "WITH Queries" to "WITH Queries (Common Table
Expressions)" because CTEs are the defining characteristic of WITH
queries, and, as I've said, the term "common table expression" has
mindshare in a way that "WITH query" clearly doesn't.
--
Regards,
Peter Geoghegan
On 29/12/10 03:35, Peter Eisentraut wrote:
On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.
Peter's comment certainly resonates with me. When I first heard about
this "CTE" business I had to go to the web to discover that they were
components of the WITH clause - which I was familiar with from my DB2
days...
regards
Mark
On 2010-12-29 09:16, Mark Kirkwood wrote:
On 29/12/10 03:35, Peter Eisentraut wrote:
On tis, 2010-12-28 at 00:19 +0000, Peter Geoghegan wrote:
It's worth noting that officially (i.e. in the docs), we don't even
call CTEs CTEs at any point. We call them WITH queries. I think that
that's a mistake because we call them CTEs everywhere else.I think "WITH query" or "WITH clause" is more understandable than CTE,
which to me is a term that has no relationship with anything else.Peter's comment certainly resonates with me. When I first heard about
this "CTE" business I had to go to the web to discover that they were
components of the WITH clause - which I was familiar with from my DB2
days...
For me it was the converse.. I first heard of Common Table Expressions
from SQLserver users, at roughly the same time that CTE's were
introduced in 8.4. When I decided to use them, it took me a while to
figure out the docs refered to it as "WITH queries".
ISTM we're already past the choice to have a single name. IMHO it would
be best if the documentation has a reference / index part in which both
WITH queries and Common Table Expressions (CTE) are listed.
Also, the terms CTE and CTEScan appear in EXPLAIN output, it would be
nice to have a meaningful hit when looking for the term in the
documentation page, instead of 'Your search for *cte* returned no hits.'
regards,
Yeb Havinga
On Wed, Dec 29, 2010 at 4:19 AM, Yeb Havinga <yebhavinga@gmail.com> wrote:
Also, the terms CTE and CTEScan appear in EXPLAIN output, it would be nice
to have a meaningful hit when looking for the term in the documentation
page, instead of 'Your search for cte returned no hits.'
This is an excellent point.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Tue, Dec 28, 2010 at 11:07:59PM +0000, Peter Geoghegan wrote:
On 28 December 2010 20:07, Peter Eisentraut <peter_e@gmx.net> wrote:
The phrase "common table expression" does not appear anywhere in the SQL
standard. �The standard uses the grammar symbol <with clause>.I think we're losing sight of the issue a bit here.
No one is proposing that we call WITH queries common table
expressions. As I think we all agree, the term "WITH query" and
"common table expression" are not synonymous. A WITH query is
comprised of one or more common table expressions, plus a conventional
SELECT query.
As of 9.1, the thing appended to the CTE(s) can be a conventional DML
query (SELECT, INSERT, UPDATE or DELETE). I'm hoping to expand this
in future versions. :)
All that I'm asking is that we /specify/ that the "subqueries"
already mentioned in the docs are common table expressions.
+1
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Dec 28, 2010 at 07:09:14AM -0500, Robert Haas wrote:
On Tue, Dec 28, 2010 at 12:45 AM, David Fetter <david@fetter.org> wrote:
I don't see how people can be relying on links to 9.1-to-be's
documentation.Well, it's always handy when the filenames are the same across
versions. Ever looked at the 9.0 documentation for something and then
modified the URL to see what it looked like in 8.1 or something?
I have occasionally wondered if it would be possible to include in the
SGML references to the same (or equivalent) page in previous versions,
so that each page in the docs could reference the equivalent page in
other versions. That would be extremely useful when trying to work out
what works in your particular version.
I've never done anything serious with SGML, but it'd be cool if it was
possible.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle
Hello,
I have added my common table expressions docpatch to the 2011-01 commitfest:
https://commitfest.postgresql.org/action/patch_view?id=476
I think that we need to get this resolved.
--
Regards,
Peter Geoghegan
Greetings,
* Peter Geoghegan (peter.geoghegan86@gmail.com) wrote:
I do this all the time. Anyway, I intend for this doc patch to be
backported to 8.4 as a bugfix, which is part of the reason why it
isn't invasive - it's just a clarification. Clearly if it makes sense
for 9.1, it makes just as much sense for 9.0 and 8.4.
I agree with the patch, in general, as well as the recommendation to
back-port it. I reviewed it and didn't find any issues (though I
couldn't figure out the right magic things to install to actually build
the docs.. :( ). The only minor change I made was to capitalize Common
Table Expressions (having it as an acronym w/o capitalizing the full
name seemed odd to me..).
Updated patch attached. Marking as ready for committer.
commit 91e9e9285752c9fbe0c222708a10a301731594c8
Author: Stephen Frost <sfrost@snowman.net>
Date: Wed Jan 19 20:56:44 2011 -0500
Update WITH documentation to capitalize acronym
Common Table Expressions, being a 'proper' name and having an
acronym associated with them, really should be capitalized. This
patch makes that change in the WITH documentation.
commit 9e4565cc97b81fd6b3f96d8e346bcb7ee6e8878e
Author: Stephen Frost <sfrost@snowman.net>
Date: Wed Jan 19 20:54:47 2011 -0500
Add CTE as an acryonym and clarify WITH docs
This patch adds CTE to the list of acronyms and then updates the
WITH documentation to note that WITH queries are also known as
CTEs.
Patch by Peter Geoghegan
Thanks,
Stephen
Attachments:
with_cte_docs.patchtext/x-diff; charset=us-asciiDownload
*** a/doc/src/sgml/acronyms.sgml
--- b/doc/src/sgml/acronyms.sgml
***************
*** 99,104 ****
--- 99,113 ----
</varlistentry>
<varlistentry>
+ <term><acronym>CTE</acronym></term>
+ <listitem>
+ <para>
+ <link linkend="queries-with">Common Table Expression</link>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><acronym>CVE</acronym></term>
<listitem>
<para>
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1525,1531 **** SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<sect1 id="queries-with">
! <title><literal>WITH</literal> Queries</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
--- 1525,1531 ----
<sect1 id="queries-with">
! <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
***************
*** 1539,1545 **** SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<para>
<literal>WITH</> provides a way to write subqueries for use in a larger
! query. The subqueries can be thought of as defining
temporary tables that exist just for this query. One use of this feature
is to break down complicated queries into simpler parts. An example is:
--- 1539,1546 ----
<para>
<literal>WITH</> provides a way to write subqueries for use in a larger
! query. The subqueries, which are often referred to as Common Table
! Expressions or <acronym>CTE</acronym>s, can be thought of as defining
temporary tables that exist just for this query. One use of this feature
is to break down complicated queries into simpler parts. An example is:
On Wed, Jan 19, 2011 at 9:13 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Peter Geoghegan (peter.geoghegan86@gmail.com) wrote:
I do this all the time. Anyway, I intend for this doc patch to be
backported to 8.4 as a bugfix, which is part of the reason why it
isn't invasive - it's just a clarification. Clearly if it makes sense
for 9.1, it makes just as much sense for 9.0 and 8.4.I agree with the patch, in general, as well as the recommendation to
back-port it. I reviewed it and didn't find any issues (though I
couldn't figure out the right magic things to install to actually build
the docs.. :( ). The only minor change I made was to capitalize Common
Table Expressions (having it as an acronym w/o capitalizing the full
name seemed odd to me..).Updated patch attached. Marking as ready for committer.
Committed.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I think that a major goal of the DocBook format is that it separates
content from presentation, so whatever tool is used to render that
content as HTML for .org isn't necessarily publicly available.
--
Regards,
Peter Geoghegan
On Thu, Jan 20, 2011 at 03:48, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:
I think that a major goal of the DocBook format is that it separates
content from presentation, so whatever tool is used to render that
content as HTML for .org isn't necessarily publicly available.
Sure it is. And FWIW, it just uses the HTML generated by the docbook
tools, we don't actually work off the SGML.
The current version can be found at
https://pgweb.postgresql.org/browser/trunk/portal/tools/docs.
The new, upcoming, version that does things like attempt-to-make-xhtml
is at http://git.postgresql.org/gitweb?p=pgweb.git;a=blob;f=tools/docs/docload.py;h=5abc2f734003d28836a85fbc0ec6c01804937af8;hb=a3523e2ba8a250950a928879ae7fa5c0a8a46d94
You will quickly notice they do basically nothing other than read in
the HTML, and then "copy/paste" it into the current framework.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/