wCTE: about the name of the feature
The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.
The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a view
Comments?
regards, tom lane
On 2011-02-24 6:20 PM +0200, Tom Lane wrote:
The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.
Agreed.
The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
Out of everything suggested so far, I think this is the best we have, if
we can fit the whole thing into out error messages. Quickly grepping
through the patch suggests that we can, at least for the cases in the
current patch.
I also prefer "statement" over "command".
Regards,
Marko Tiikkaja
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:20 PM +0200, Tom Lane wrote:
The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
Out of everything suggested so far, I think this is the best we have, if
we can fit the whole thing into out error messages. Quickly grepping
through the patch suggests that we can, at least for the cases in the
current patch.
I also prefer "statement" over "command".
OK, I will make those adjustments. Are you going to do more work on the
documentation part of the patch? I can stick to working on the code
part meanwhile, if you are.
regards, tom lane
On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
OK, I will make those adjustments. Are you going to do more work on the
documentation part of the patch? I can stick to working on the code
part meanwhile, if you are.
I am planning on working on the documentation this weekend.
Regards,
Marko Tiikkaja
On 02/24/2011 11:20 AM, Tom Lane wrote:
The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
I think your're absolutely right. DML means that to me too. It's in
effect the opposite of DDL.
log_statement used "mod" for this category of statements. If we need a
new acronym, I modestly suggest "CUD" (CRUD without the R). The we could
make all sorts of puns about "chewing the CUD" :-)
cheers
andrew
On Thu, Feb 24, 2011 at 11:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
Great idea. I had the same complaint when I looked at this patch a
year ago, but didn't come up with nearly as good an idea as to what to
do about it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Feb 24, 2011, at 10:43 AM, Robert Haas wrote:
The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
Great idea. I had the same complaint when I looked at this patch a
year ago, but didn't come up with nearly as good an idea as to what to
do about it.
I like "statement" better than "command," too, but love the acronym DMC. As in, "you want to Run [a] DMC." ;-P
David
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
OK, I will make those adjustments. Are you going to do more work on the
documentation part of the patch? I can stick to working on the code
part meanwhile, if you are.
I am planning on working on the documentation this weekend.
I've gone ahead and applied the code portion of the patch, with
modifications as per discussion, and other editorialization.
I'll wait on you to produce documentation updates before dealing
with the docs, but I figured we might as well get some buildfarm
cycles on it meanwhile.
regards, tom lane
On 2011-02-26 2:00 AM, Tom Lane wrote:
I've gone ahead and applied the code portion of the patch, with
modifications as per discussion, and other editorialization.
Thanks a lot!
One thing bothers me though: what was the reason for requiring a
RETURNING clause for data-modifying statements in WITH?
I'll wait on you to produce documentation updates before dealing
with the docs, but I figured we might as well get some buildfarm
cycles on it meanwhile.
Thanks, I'll send an improved version tomorrow.
Regards,
Marko Tiikkaja
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-26 2:00 AM, Tom Lane wrote:
I've gone ahead and applied the code portion of the patch, with
modifications as per discussion, and other editorialization.
Thanks a lot!
One thing bothers me though: what was the reason for requiring a
RETURNING clause for data-modifying statements in WITH?
That test was in your patch, no? I moved the code to another place
but it's still enforcing the same thing, namely that you can't reference
the output of an INSERT/UPDATE/DELETE that hasn't got RETURNING.
regards, tom lane
On Thu, Feb 24, 2011 at 11:20:48AM -0500, Tom Lane wrote:
The wCTE patch refers to the feature it's adding as "DML WITH". I'm
still pretty unhappy with that terminology. In my view of the world,
"DML" includes SELECT as well as INSERT/UPDATE/DELETE. The wikipedia
entry about the term
http://en.wikipedia.org/wiki/Data_Manipulation_Language
agrees that that's at least the majority usage, and even our own docs
seem to use it to include SELECT as often as not. Since the distinction
is absolutely critical to talking about this feature sensibly, I don't
think it's a good plan to use an acronym that is guaranteed to produce
uncertainty in the reader's mind.The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
+1
If we ever decide add in what I'd originally envisioned, namely DCL
and DDL, the name continues to describe what's going on :)
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 Thu, Feb 24, 2011 at 11:35:30AM -0800, David Wheeler wrote:
On Feb 24, 2011, at 10:43 AM, Robert Haas wrote:
The best idea I have at the moment is to spell out "data modifying
command" (or "statement") rather than relying on the acronym.
In the code, we could change hasDmlWith to hasModifyingWith, for
example. The error messages could read like
data-modifying statement in WITH is not allowed in a viewComments?
Great idea. I had the same complaint when I looked at this patch
a year ago, but didn't come up with nearly as good an idea as to
what to do about it.I like "statement" better than "command," too, but love the acronym
DMC. As in, "you want to Run [a] DMC." ;-P
Hit it, Run! ;)
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 Fri, Feb 25, 2011 at 7:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:37 PM +0200, Tom Lane wrote:
OK, I will make those adjustments. Are you going to do more work on the
documentation part of the patch? I can stick to working on the code
part meanwhile, if you are.I am planning on working on the documentation this weekend.
I've gone ahead and applied the code portion of the patch,
Yay! I'm excited about this, particularly the possible "pipelining"
stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... and
have it be like cool and fast and stuff.
Or at least I hope you can do that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
Yay! I'm excited about this, particularly the possible "pipelining"
stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... and
have it be like cool and fast and stuff.
Or at least I hope you can do that.
It's gonna need some work yet. As things stand, the tuples are indeed
pipelined through, but the CteScan nodes *also* stash them aside into
tuplestores, just in case somebody demands a rescan. Fixing that will
require revisiting the exec flags (EXEC_FLAG_REWIND etc). We don't
currently distinguish "it's unlikely you'll have to rescan" from
"you're guaranteed not to have to rescan", but a CteScan that's covering
a ModifyTable has to know the latter to not have to keep hold of copies
of the RETURNING tuples.
It might be a small enough change to do after alpha starts, but I don't
have time for it right now.
regards, tom lane
On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Yay! I'm excited about this, particularly the possible "pipelining"
stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... and
have it be like cool and fast and stuff.Or at least I hope you can do that.
It's gonna need some work yet. As things stand, the tuples are indeed
pipelined through, but the CteScan nodes *also* stash them aside into
tuplestores, just in case somebody demands a rescan. Fixing that will
require revisiting the exec flags (EXEC_FLAG_REWIND etc). We don't
currently distinguish "it's unlikely you'll have to rescan" from
"you're guaranteed not to have to rescan", but a CteScan that's covering
a ModifyTable has to know the latter to not have to keep hold of copies
of the RETURNING tuples.It might be a small enough change to do after alpha starts, but I don't
have time for it right now.
Well, if nothing else, the potential is there for a future release.
I'm probably not quite as excited about this feature as David Fetter
(and my 100-Watt lightbulb is not quite as bright as the sun at high
noon in midsummer) but I do think it's pretty cool, and I appreciate
you getting it in, even in a somewhat basic form.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 2011-02-26 4:41 AM +0200, Tom Lane wrote:
Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
One thing bothers me though: what was the reason for requiring a
RETURNING clause for data-modifying statements in WITH?That test was in your patch, no? I moved the code to another place
but it's still enforcing the same thing, namely that you can't reference
the output of an INSERT/UPDATE/DELETE that hasn't got RETURNING.
Oh, right. I misunderstood.
Regards,
Marko Tiikkaja
On Sat, Feb 26, 2011 at 12:52:40AM -0500, Robert Haas wrote:
On Sat, Feb 26, 2011 at 12:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Yay! �I'm excited about this, particularly the possible "pipelining"
stuff, where you can do WITH (DELETE .. RETURNING ..) INSERT ... �and
have it be like cool and fast and stuff.Or at least I hope you can do that.
It's gonna need some work yet. �As things stand, the tuples are indeed
pipelined through, but the CteScan nodes *also* stash them aside into
tuplestores, just in case somebody demands a rescan. �Fixing that will
require revisiting the exec flags (EXEC_FLAG_REWIND etc). �We don't
currently distinguish "it's unlikely you'll have to rescan" from
"you're guaranteed not to have to rescan", but a CteScan that's covering
a ModifyTable has to know the latter to not have to keep hold of copies
of the RETURNING tuples.It might be a small enough change to do after alpha starts, but I don't
have time for it right now.Well, if nothing else, the potential is there for a future release.
That's kinda where I am on this one. In the grand PostgreSQL
tradition, we can have something that works before we have something
that works fast :)
I'm probably not quite as excited about this feature as David Fetter
Heh!
(and my 100-Watt lightbulb is not quite as bright as the sun at high
noon in midsummer) but I do think it's pretty cool, and I appreciate
you getting it in, even in a somewhat basic form.
Me, too!
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 2011-02-24 6:40 PM, I wrote:
I am planning on working on the documentation this weekend.
And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.
I tried to be more strict about using "subquery" when talking about
WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.
Regards,
Marko Tiikkaja
Attachments:
dmswithdoc1.patchtext/plain; charset=iso-8859-1; name=dmswithdoc1.patchDownload
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1526,1532 **** SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
<sect1 id="queries-with">
! <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
--- 1526,1532 ----
<sect1 id="queries-with">
! <title><literal>WITH</literal> Statements (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
***************
*** 1539,1549 **** 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, 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:
<programlisting>
WITH regional_sales AS (
--- 1539,1559 ----
</indexterm>
<para>
! <literal>WITH</> provides a way to write auxiliary statements for use in a
! larger query. These statements, 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.
! </para>
!
! <sect2 id="subqueries-with">
! <title>SELECT Queries</title>
!
! <indexterm>
! <primary>*</primary>
! </indexterm>
! <para>
! One use of this feature is to break down complicated queries into simpler
! parts. An example is:
<programlisting>
WITH regional_sales AS (
***************
*** 1806,1811 **** SELECT n FROM t LIMIT 100;
--- 1816,1917 ----
In each case it effectively provides temporary table(s) that can
be referred to in the main command.
</para>
+ </sect2>
+ <sect2 id="modifying-with">
+ <title>Data-Modifying Statements</title>
+
+ <indexterm>
+ <primary>*</primary>
+ </indexterm>
+
+ <para>
+ You can also use data-modifying statements <command>INSERT</>,
+ <command>UPDATE</> and <command>DELETE</> in <literal>WITH</>. This allows
+ you to perform many different operations in the same query. An example is:
+
+ <programlisting>
+ WITH moved_rows AS (
+ DELETE FROM ONLY products
+ WHERE
+ "date" >= '2010-10-01' AND
+ "date" < '2010-11-01'
+ RETURNING *
+ )
+ INSERT INTO products_log
+ SELECT * FROM moved_rows;
+ </programlisting>
+
+ which moves rows from "products" to "products_log". In the example above,
+ the <literal>WITH</> clause is attached to the <command>INSERT</>, not the
+ <command>SELECT</>. This is important, because data-modifying statements
+ are not allowed in <literal>WITH</> clauses which are not attached to the
+ top level statement. However, normal <literal>WITH</> visibility rules
+ apply: it is possible to refer to a data-modifying <literal>WITH</> from a
+ subquery.
+ </para>
+
+ <para>
+ Recursive self-references in data-modifying statements are not
+ allowed. In some cases it is possible to work around this limitation by
+ referring to the output of a recursive <literal>WITH</>:
+
+ <programlisting>
+ WITH RECURSIVE included_parts(sub_part, part) AS (
+ SELECT sub_part, part FROM parts WHERE part = 'our_product'
+ UNION ALL
+ SELECT p.sub_part, p.part
+ FROM included_parts pr, parts p
+ WHERE p.part = pr.sub_part
+ )
+ DELETE FROM parts
+ WHERE part IN (SELECT part FROM included_parts);
+ </programlisting>
+
+ The above query would remove all direct and indirect subparts of a product.
+ </para>
+
+ <para>
+ The execution of data-modifying statements in <literal>WITH</> is
+ interleaved with the main plan, and the order in which the statements
+ are executed is arbitrary. The changes made by data-modifying statements
+ are not visible to the query.
+ </para>
+ <important>
+ <para>
+ Trying to update the same row twice in a single command is not supported.
+ Only one of the modifications takes place, but it is not easy (and
+ sometimes not possible) to reliably predict which one. This also applies
+ to deleting a row that was already updated in the same command; only the
+ update is performed. You should generally avoid trying to modify a single
+ row twice in a single command.
+ </para>
+ </important>
+
+ <para>
+ Data-modifying statements are executed exactly once, and always to
+ completion. If a <literal>WITH</> containing a data-modifying statement
+ is not referred to in the query, it is possible to omit the
+ <literal>RETURNING</> clause:
+
+ <programlisting>
+ WITH t AS (
+ DELETE FROM foo
+ )
+ DELETE FROM bar;
+ </programlisting>
+
+ The example above would remove all rows from tables "foo" and "bar". The
+ number of affected rows returned to the client would only include rows
+ removed from "bar".
+ </para>
+
+ <para>
+ Any table used as the target of a data-modifying statement in
+ <literal>WITH</> must not contain a conditional rule, an <literal>ALSO</>
+ rule or an <literal>INSTEAD</> rule with multiple statements.
+ </para>
+
+ </sect2>
</sect1>
*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 89,95 **** DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>DELETE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 89,95 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the <command>DELETE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 90,96 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>INSERT</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 90,96 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the <command>INSERT</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 58,64 **** SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
! <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
--- 58,64 ----
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
! <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
***************
*** 206,233 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the primary query.
! The subqueries effectively act as temporary tables or views
! for the duration of the primary query.
</para>
<para>
A name (without schema qualification) must be specified for each
! <literal>WITH</literal> query. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the subquery.
</para>
<para>
If <literal>RECURSIVE</literal> is specified, it allows a
! subquery to reference itself by name. Such a subquery must have
! the form
<synopsis>
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
</synopsis>
where the recursive self-reference must appear on the right-hand
side of the <literal>UNION</>. Only one recursive self-reference
! is permitted per query.
</para>
<para>
--- 206,241 ----
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! read-only (<literal>SELECT</literal>) or data-modifying statements whose
! results can be referenced by name in the primary query. These statements
! effectively act as temporary tables or views for the duration of the
! primary query. Currently only <literal>SELECT</literal>,
! <literal>INSERT</literal>, <literal>UPDATE</literal> and
! <literal>DELETE</literal> statements are supported in
! <literal>WITH</literal>.
</para>
<para>
A name (without schema qualification) must be specified for each
! <literal>WITH</literal> statement. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the statement.
</para>
<para>
If <literal>RECURSIVE</literal> is specified, it allows a
! <literal>SELECT</literal> subquery to reference itself by name. Such a
! subquery must have the form
<synopsis>
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL | DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
</synopsis>
where the recursive self-reference must appear on the right-hand
side of the <literal>UNION</>. Only one recursive self-reference
! is permitted per query. Recursive data-modifying statements are not
! supported, but you can use the results of a recursive
! <literal>SELECT</literal> query in
! a data-modifying statement. See <xref linkend="queries-with"> for
! an example.
</para>
<para>
***************
*** 241,247 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] |
</para>
<para>
! A useful property of <literal>WITH</literal> queries is that they
are evaluated only once per execution of the primary query,
even if the primary query refers to them more than once.
</para>
--- 249,255 ----
</para>
<para>
! A useful property of <literal>WITH</literal> statements is that they
are evaluated only once per execution of the primary query,
even if the primary query refers to them more than once.
</para>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 85,91 **** UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>UPDATE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 85,91 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the <command>UPDATE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:40 PM, I wrote:
I am planning on working on the documentation this weekend.
And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.
Thanks, will work on this next.
regards, tom lane
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:40 PM, I wrote:
I am planning on working on the documentation this weekend.
And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.
Applied after some rather heavy editorialization.
I tried to be more strict about using "subquery" when talking about
WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.
I undid most of those changes --- it didn't seem to add anything to be
strict in this way, and anyway you hadn't done it consistently,
eg the syntax still had "with_query".
regards, tom lane
On 2011-02-28 8:20 AM +0200, Tom Lane wrote:
Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi> writes:
On 2011-02-24 6:40 PM, I wrote:
I am planning on working on the documentation this weekend.
And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.Applied after some rather heavy editorialization.
Thanks again.
I tried to be more strict about using "subquery" when talking about
WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.I undid most of those changes --- it didn't seem to add anything to be
strict in this way, and anyway you hadn't done it consistently,
eg the syntax still had "with_query".
I wasn't so sure about those changes either. It does seem more
consistent this way.
Regards,
Marko Tiikkaja