On 08/06/2013 11:03 PM, Karl O. Pinc wrote:
> The attached documentation patch, doc-subqueries-v1.patch,
> applies against head.
>
> I wanted to document that subqueries can't modify data.
> This is mentioned in the documentation for SELECT and
> implied elsewhere but I was looking for something more
> than an 'in-passing' mention.
>
> (I wrote a bad query,
> modifying data in a subquery, couldn't recall where
> it was documented that you can't do this, and couldn't
> find the answer from the TOC or the index. Now that
> there's lots of statements with RETURNING clauses
> it's natural to want to use them in subqueries.)
Hello, I am (finally) reviewing this patch.
After reading your reasoning, David's rebuttal, and the patch itself;
I'm wondering if this is needed or wanted at all.
Supposing it is wanted, it creates more questions than it answers. The
two biggies are:
* In what other contexts can tabular subqueries be used?
* What are other ways of integrating data returned by data modification
statements?
On a superficial level I find the number of commas a bit clunky, and
"parentheses" is misspelled.
> The last 2 sentences of the first paragraph are
> something in the way of helpful hints and may not
> be appropriate, or even accurate. I've left them in
> for review.
I think the last sentence (of the first paragraph) is a bit much, but
the penultimate seems fine.
I'm attaching an updated patch that I think is an improvement but it's
still at a draft level and needs more copyediting. This new patch does
not attempt to answer the two questions above.
--
Vik
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index c32c857..b134b66 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -549,7 +549,7 @@ SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
</programlisting>
Additionally, an alias is required if the table reference is a
- subquery (see <xref linkend="queries-subqueries">).
+ subquery (see <xref linkend="queries-subquery-derived-tables">).
</para>
<para>
@@ -590,10 +590,10 @@ SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
</para>
</sect3>
- <sect3 id="queries-subqueries">
- <title>Subqueries</title>
+ <sect3 id="queries-subquery-derived-tables">
+ <title>Subquery Derived Tables</title>
- <indexterm zone="queries-subqueries">
+ <indexterm zone="queries-subquery-derived-tables">
<primary>subquery</primary>
</indexterm>
@@ -1315,6 +1315,44 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab
</sect1>
+ <sect1 id="queries-subqueries">
+ <title>Subqueries</title>
+
+ <indexterm zone="queries-subqueries">
+ <primary>subquery</primary>
+ </indexterm>
+
+ <indexterm zone="queries-subqueries">
+ <primary>sub-select</primary>
+ </indexterm>
+
+ <para>
+ Subqueries, also called sub-selects, are queries written within
+ parentheses in the text of larger queries. The values produced by
+ subqueries may be scalar, or tabular. Scalar subqueries are used within expressions as described
+ in <xref linkend="sql-syntax-scalar-subqueries">.
+ Tabular subqueries may substitute for tables as described
+ in <xref linkend="queries-subquery-derived-tables">, generate array
+ content as described
+ in <xref linkend="sql-syntax-array-constructors">, have their
+ result content tested within expressions as described
+ in <xref linkend="functions-subquery">, or be used in other
+ contexts. Often either joins or subqueries can be used to produce
+ different query plans yielding identical output.
+ </para>
+
+ <para>
+ Subqueries may not modify database
+ content. <link linkend="queries-with">Common Table
+ Expressions</link> are one way to integrate data returned by data
+ modification statements,
+ i.e. <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>
+ statements with <literal>RETURNING</literal> clauses, into larger
+ queries.
+ </para>
+ </sect1>
+
+
<sect1 id="queries-union">
<title>Combining Queries</title>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
