WITH
問い合わせ(共通テーブル式) #
<literal>WITH</literal> 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 one query. Each auxiliary statement
in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
<command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
or <command>MERGE</command>; and the
<literal>WITH</literal> clause itself is attached to a primary statement that can
also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, or <command>MERGE</command>.
《マッチ度[91.376912]》WITH
は、より大規模な問い合わせで使用される補助文を記述する方法を提供します。
これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、1つの問い合わせのために存在する一時テーブルを定義すると考えることができます。
WITH
句内の補助文はそれぞれSELECT
、INSERT
、UPDATE
またはDELETE
を取ることができます。
そしてWITH
句自身は、これもSELECT
、INSERT
、UPDATE
、DELETE
、またはMERGE
を取ることができる主文に付与されます。
WITH
内のSELECT
#
The basic value of <command>SELECT</command> in <literal>WITH</literal> is to
break down complicated queries into simpler parts. An example is:
WITH
内のSELECT
の基本的な価値は、複雑な問い合わせをより単純な部品に分解することです。
以下に例を示します。
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
which displays per-product sales totals in only the top sales regions.
The <literal>WITH</literal> clause defines two auxiliary statements named
<structname>regional_sales</structname> and <structname>top_regions</structname>,
where the output of <structname>regional_sales</structname> is used in
<structname>top_regions</structname> and the output of <structname>top_regions</structname>
is used in the primary <command>SELECT</command> query.
This example could have been written without <literal>WITH</literal>,
but we'd have needed two levels of nested sub-<command>SELECT</command>s. It's a bit
easier to follow this way.
これは販売トップの地域(region)のみから製品ごとの売上高を表示します。
WITH
句は、regional_sales
、top_regions
という名前の2つの補助文を定義します。
ここで、regional_sales
の出力はtop_regions
内で使用され、top_regions
はSELECT
主問い合わせで使用されます。
この例は WITH
なしでも記述できますが、二階層の入れ子のsub-SELECT
を必要とします。この方法に従うほうが多少扱いやすいです。
The optional <literal>RECURSIVE</literal> modifier changes <literal>WITH</literal>
from a mere syntactic convenience into a feature that accomplishes
things not otherwise possible in standard SQL. Using
<literal>RECURSIVE</literal>, a <literal>WITH</literal> query can refer to its own
output. A very simple example is this query to sum the integers from 1
through 100:
オプションのRECURSIVE
修飾子は、WITH
を、単に構文上の利便性の高めるだけでなく標準的なSQLでは不可能な機能を実現させます。
RECURSIVE
を使用すれば、WITH
問い合わせが行った自己の結果を参照できるようになります。1から100までの数を合計する非常に単純な問い合わせは以下のようなものです。
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;
The general form of a recursive <literal>WITH</literal> query is always a
<firstterm>non-recursive term</firstterm>, then <literal>UNION</literal> (or
<literal>UNION ALL</literal>), then a
<firstterm>recursive term</firstterm>, where only the recursive term can contain
a reference to the query's own output. Such a query is executed as
follows:
再帰的WITH
問い合わせの汎用形式は常に、非再帰的表現(non-recursive term)、そしてUNION
(またはUNION ALL
)、そして再帰的表現(recursive term)です。
再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。
このような問い合わせは以下のように実行されます。
再帰的問い合わせの評価
<title>Recursive Query Evaluation</title>
Evaluate the non-recursive term. For <literal>UNION</literal> (but not
<literal>UNION ALL</literal>), discard duplicate rows. Include all remaining
rows in the result of the recursive query, and also place them in a
temporary <firstterm>working table</firstterm>.
非再帰的表現を評価します。
UNION
(ただしUNION ALL
は除きます)では、重複行を廃棄します。
その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時作業テーブルに置きます。
So long as the working table is not empty, repeat these steps: 作業テーブルが空でないのであれば以下の手順を繰り返します。
Evaluate the recursive term, substituting the current contents of
the working table for the recursive self-reference.
For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard
duplicate rows and rows that duplicate any previous result row.
Include all remaining rows in the result of the recursive query, and
also place them in a temporary <firstterm>intermediate table</firstterm>.
再帰自己参照を作業テーブルの実行中の内容で置換し、再帰的表現を評価します。
UNION
(ただしUNION ALL
は除きます)に対し、重複行と前の結果行と重複する行を破棄します。
その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時中間テーブルに置きます。
Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table. 中間テーブルの内容で作業テーブルの内容を差し替え、中間テーブルを空にします。
While <literal>RECURSIVE</literal> allows queries to be specified
recursively, internally such queries are evaluated iteratively.
RECURSIVE
では問い合わせを再帰的(recursively)に指定できますが、内部的にはそのような問い合わせは反復的(iteratively)に評価されます。
In the example above, the working table has just a single row in each step,
and it takes on the values from 1 through 100 in successive steps. In
the 100th step, there is no output because of the <literal>WHERE</literal>
clause, and so the query terminates.
上記の例で、作業テーブルはそれぞれの手順での単なる単一行で、引き続く作業で1から100までの値を獲得します。
100番目の作業で、WHERE
句による出力が無くなり、問い合わせが終了します。
Recursive queries are typically used to deal with hierarchical or tree-structured data. A useful example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions: 再帰的問い合わせは階層的、またはツリー構造データに対処するため一般的に使用されます。 実用的な例は、直接使用する部品を表すテーブル1つのみが与えられ、そこから製品すべての直接・間接部品を見つける次の問い合わせです。
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity * pr.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) SELECT sub_part, SUM(quantity) as total_quantity FROM included_parts GROUP BY sub_part
When computing a tree traversal using a recursive query, you might want to order the results in either depth-first or breadth-first order. This can be done by computing an ordering column alongside the other data columns and using that to sort the results at the end. Note that this does not actually control in which order the query evaluation visits the rows; that is as always in SQL implementation-dependent. This approach merely provides a convenient way to order the results afterwards. 再帰的問い合わせを使用してツリーの巡回順を計算する場合、深さ優先または幅優先のいずれかの順序で結果を順序付ける必要がある場合があります。 これは、他のデータ列と並んで順序付け列を計算し、それを使用して最後に結果を並べ替えることで実行できます。 これは、問合せ評価が行を訪問する順序を実際に制御するものではなく、常にSQL実装に依存することに注意してください。 このアプローチは、結果を後で順序付けるための便利な方法を提供するにすぎません。
To create a depth-first order, we compute for each result row an array of
rows that we have visited so far. For example, consider the following
query that searches a table <structname>tree</structname> using a
<structfield>link</structfield> field:
深さ優先順序を作成するには、結果行ごとに、これまでに訪れた行の配列を計算します。
例えば、link
フィールドを使用してテーブルtree
を検索する次の問合せを考えてみます。
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree;
To add depth-first ordering information, you can write this: 深さ優先の順序付け情報を追加するには、次のように記述します。
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[t.id] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || t.id FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
In the general case where more than one field needs to be used to identify
a row, use an array of rows. For example, if we needed to track fields
<structfield>f1</structfield> and <structfield>f2</structfield>:
行を識別するために複数のフィールドを使用する必要がある一般的な場合は、行の配列を使用します。
たとえば、フィールドf1
とf2
を追跡する必要がある場合は、次のようにします。
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2) FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
Omit the <literal>ROW()</literal> syntax in the common case where only one
field needs to be tracked. This allows a simple array rather than a
composite-type array to be used, gaining efficiency.
追跡する必要があるフィールドが1つだけである一般的な場合では、ROW()
構文を削除します。
これで、複合型配列ではなく単純配列で済むので、効率も上がります。
To create a breadth-first order, you can add a column that tracks the depth of the search, for example: 幅優先順序を作成するには、検索の深さを追跡する列を追加します。次に例を示します。
WITH RECURSIVE search_tree(id, link, data, depth) AS ( SELECT t.id, t.link, t.data, 0 FROM tree t UNION ALL SELECT t.id, t.link, t.data, depth + 1 FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY depth;
To get a stable sort, add data columns as secondary sorting columns. 安定した並べ替えを行うには、データ列を2次並べ替え列として追加します。
The recursive query evaluation algorithm produces its output in breadth-first search order. However, this is an implementation detail and it is perhaps unsound to rely on it. The order of the rows within each level is certainly undefined, so some explicit ordering might be desired in any case. 再帰的問い合わせ評価アルゴリズムは、幅優先の検索順で出力を生成します。 しかし、これは実装の詳細であり、これに頼るのはおそらく不健全です。 各レベル内の行の順序は確かに未定義であるため、いかなる場合でも明示的な順序付けが望まれるかもしれません。
There is built-in syntax to compute a depth- or breadth-first sort column. For example: 深さ優先または幅優先の並べ替え列を計算するための組み込み構文があります。 例えば、
WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol; WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH BREADTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;
This syntax is internally expanded to something similar to the above
hand-written forms. The <literal>SEARCH</literal> clause specifies whether
depth- or breadth first search is wanted, the list of columns to track for
sorting, and a column name that will contain the result data that can be
used for sorting. That column will implicitly be added to the output rows
of the CTE.
この構文は、上記の手書きの形式に似たものに内部的に拡張されています。
SEARCH
句は、深さ優先または幅優先のどちらの検索が必要か、並べ替えのために追跡する列のリスト、並べ替えに使用できる結果データを含む列名を指定します。
この列は、CTEの出力行に暗黙的に追加されます。
When working with recursive queries it is important to be sure that
the recursive part of the query will eventually return no tuples,
or else the query will loop indefinitely. Sometimes, using
<literal>UNION</literal> instead of <literal>UNION ALL</literal> can accomplish this
by discarding rows that duplicate previous output rows. However, often a
cycle does not involve output rows that are completely duplicate: it may be
necessary to check just one or a few fields to see if the same point has
been reached before. The standard method for handling such situations is
to compute an array of the already-visited values. For example, consider again
the following query that searches a table <structname>graph</structname> using a
<structfield>link</structfield> field:
再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。
そうしなければ、問い合わせが永久にループしてしまうからです。
UNION ALL
の替わりにUNION
を使用することで、重複する前回の出力行が廃棄され、これを実現できることもあるでしょう。
しかし、各周期が完全に重複している行を含まないこともよくあり、そのような場合は、1つまたは少数のフィールドを検査して、同じ場所に既に到達したかどうかを調べる必要があるかもしれません。
このような状態を取り扱う標準手法は、既に巡回された値の配列を計算することです。
例えば、link
フィールドを使ってテーブルgraph
を検索する以下の問い合わせを考えて見ます。
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 0 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) SELECT * FROM search_graph;
This query will loop if the <structfield>link</structfield> relationships contain
cycles. Because we require a <quote>depth</quote> output, just changing
<literal>UNION ALL</literal> to <literal>UNION</literal> would not eliminate the looping.
Instead we need to recognize whether we have reached the same row again
while following a particular path of links. We add two columns
<structfield>is_cycle</structfield> and <structfield>path</structfield> to the loop-prone query:
この問い合わせはlink
関係が循環を含んでいればループします。
「depth」出力を要求しているので、UNION ALL
をUNION
に変えるだけでは、ループを取り除くことができません。
その代わり、linkの特定の経路をたどっている間に、同じ行に到達したかどうかを認識する必要があります。
このループしやすい問い合わせに、path
とcycle
の2列を加えます。
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[g.id] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, g.id = ANY(path), path || g.id FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
Aside from preventing cycles, the array value is often useful in its own right as representing the <quote>path</quote> taken to reach any particular row. 巡回防止の他に、特定行に到達する際に選ばれた「path」 それ自体を表示するため、配列値はしばしば利用価値があります。
In the general case where more than one field needs to be checked to
recognize a cycle, use an array of rows. For example, if we needed to
compare fields <structfield>f1</structfield> and <structfield>f2</structfield>:
循環を認識するために検査するために必要なフィールドが複数存在する一般的な状況では、行の配列を使用します。
例えば、フィールドf1
とf2
を比較する必要があるときは次のようにします。
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[ROW(g.f1, g.f2)] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, ROW(g.f1, g.f2) = ANY(path), path || ROW(g.f1, g.f2) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
Omit the <literal>ROW()</literal> syntax in the common case where only one field
needs to be checked to recognize a cycle. This allows a simple array
rather than a composite-type array to be used, gaining efficiency.
循環を認識するために検査するために必要なフィールドが1つだけである一般的な場合では、ROW()
構文を削除します。
これで、複合型配列ではなく単純配列で済むので、効率も上がります。
There is built-in syntax to simplify cycle detection. The above query can also be written like this: サイクル検出を簡略化する組み込み構文があります。 上記のクエリは、次のように記述することもできます。
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
and it will be internally rewritten to the above form. The
<literal>CYCLE</literal> clause specifies first the list of columns to
track for cycle detection, then a column name that will show whether a
cycle has been detected, and finally the name of another column that will track the
path. The cycle and path columns will implicitly be added to the output
rows of the CTE.
また、内部的に上記の形式に書き換えられます。
CYCLE
句は、最初にサイクル検出のために追跡する列のリストを指定し、次にサイクルが検出されたかどうかを示す列名、最後にパスを追跡する別の列の名前を指定します。
サイクル列とパス列は、CTEの出力行に暗黙的に追加されます。
The cycle path column is computed in the same way as the depth-first
ordering column show in the previous section. A query can have both a
<literal>SEARCH</literal> and a <literal>CYCLE</literal> clause, but a
depth-first search specification and a cycle detection specification would
create redundant computations, so it's more efficient to just use the
<literal>CYCLE</literal> clause and order by the path column. If
breadth-first ordering is wanted, then specifying both
<literal>SEARCH</literal> and <literal>CYCLE</literal> can be useful.
サイクル・パス列は、前のセクションで示した深さ優先順序列と同じ方法で計算されます。
問い合わせにはSEARCH
句とCYCLE
句の両方を含めることができますが、深さ優先検索指定とサイクル検出指定では冗長な計算が作成されるため、CYCLE
句を使用してパス列で順序付けるだけの方が効率的です。
幅優先順序が必要な場合は、SEARCH
とCYCLE
の両方を指定すると便利です。
A helpful trick for testing queries
when you are not certain if they might loop is to place a <literal>LIMIT</literal>
in the parent query. For example, this query would loop forever without
the <literal>LIMIT</literal>:
ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせにLIMIT
を配置します。
例えば、以下の問い合わせはLIMIT
がないと永久にループします。
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
This works because <productname>PostgreSQL</productname>'s implementation
evaluates only as many rows of a <literal>WITH</literal> query as are actually
fetched by the parent query. Using this trick in production is not
recommended, because other systems might work differently. Also, it
usually won't work if you make the outer query sort the recursive query's
results or join them to some other table, because in such cases the
outer query will usually try to fetch all of the <literal>WITH</literal> query's
output anyway.
これが動作するのは、PostgreSQLの実装が、実際に親問い合わせで取り出されるのと同じ数のWITH
問い合わせの行のみを評価するからです。
この秘訣を実稼働環境で使用することは勧められません。
他のシステムでは異なった動作をする可能性があるからです。
同時に、もし外部問い合わせを再帰的問い合わせの結果を並べ替えたり、またはそれらを他のテーブルと結合するような書き方をした場合、動作しません。
このような場合、外部問い合わせは通常、WITH
問い合わせの出力をとにかくすべて取り込もうとするからです。
A useful property of <literal>WITH</literal> queries is that they are
normally evaluated only once per execution of the parent query, even if
they are referred to more than once by the parent query or
sibling <literal>WITH</literal> queries.
Thus, expensive calculations that are needed in multiple places can be
placed within a <literal>WITH</literal> query to avoid redundant work. Another
possible application is to prevent unwanted multiple evaluations of
functions with side-effects.
However, the other side of this coin is that the optimizer is not able to
push restrictions from the parent query down into a multiply-referenced
<literal>WITH</literal> query, since that might affect all uses of the
<literal>WITH</literal> query's output when it should affect only one.
The multiply-referenced <literal>WITH</literal> query will be
evaluated as written, without suppression of rows that the parent query
might discard afterwards. (But, as mentioned above, evaluation might stop
early if the reference(s) to the query demand only a limited number of
rows.)
有用なWITH
問い合わせの特性は、親問い合わせ、もしくは兄弟WITH
問い合わせによりたとえ1回以上参照されるとしても、通常は親問い合わせ実行で1回だけ評価されることです。
したがって、複数の場所で必要な高価な計算は、冗長作業を防止するためWITH
問い合わせの中に配置することができます。
他にありうる適用としては、望まれない副作用のある関数の多重評価を避けることです。
しかし、反対の見方をすれば、オプティマイザが親クエリから複数参照されるWITH
問い合わせに制約を押し下げることができないということになります。
これは、WITH
問い合わせの出力が1つのみに影響する場合、その出力のすべての使用に影響する可能性があるためです。
複数参照されるWITH
問い合わせは、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。
(しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。)
However, if a <literal>WITH</literal> query is non-recursive and
side-effect-free (that is, it is a <literal>SELECT</literal> containing
no volatile functions) then it can be folded into the parent query,
allowing joint optimization of the two query levels. By default, this
happens if the parent query references the <literal>WITH</literal> query
just once, but not if it references the <literal>WITH</literal> query
more than once. You can override that decision by
specifying <literal>MATERIALIZED</literal> to force separate calculation
of the <literal>WITH</literal> query, or by specifying <literal>NOT
MATERIALIZED</literal> to force it to be merged into the parent query.
The latter choice risks duplicate computation of
the <literal>WITH</literal> query, but it can still give a net savings if
each usage of the <literal>WITH</literal> query needs only a small part
of the <literal>WITH</literal> query's full output.
しかし、WITH
問い合わせが非再帰で副作用がない(つまり、揮発性(volatile)の関数を含まないSELECT
である)場合は、親問い合わせに組み込むことができ、2つの問い合わせレベルを同時に最適化できます。
デフォルトでは、親問い合わせがWITH
問い合わせを1回だけ参照する場合にこれが発生しますが、WITH
問い合わせを2回以上参照する場合には発生しません。
この決定を上書きするには、MATERIALIZED
を指定してWITH
問い合わせの個別の計算を強制するか、NOT MATERIALIZED
を指定して親問い合わせにマージするようにします。
後者を選択すると、WITH
問い合わせの計算が重複する危険性がありますが、WITH
問い合わせを使用するたびにWITH
問い合わせのごく一部しか必要としない場合は、全体の節約になります。
A simple example of these rules is これらのルールの簡単な例を次に示します。
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w WHERE key = 123;
This <literal>WITH</literal> query will be folded, producing the same
execution plan as
このWITH
問い合わせは組み込まれ、次のものと同じ実行計画を生成します。
SELECT * FROM big_table WHERE key = 123;
In particular, if there's an index on <structfield>key</structfield>,
it will probably be used to fetch just the rows having <literal>key =
123</literal>. On the other hand, in
特に、key
インデックスがある場合、key = 123
を持つ行のみをフェッチするために使用される可能性があります。
一方で、
WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
the <literal>WITH</literal> query will be materialized, producing a
temporary copy of <structname>big_table</structname> that is then
joined with itself — without benefit of any index. This query
will be executed much more efficiently if written as
このWITH
問い合わせでは実体化され、big_table
の一時的なコピーが生成されます。このコピーはインデックスのメリットなしに、それ自体に結合されます。
この問い合わせは次のように記述すると、より効率的に実行されます。
WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table ) SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref WHERE w2.key = 123;
so that the parent query's restrictions can be applied directly
to scans of <structname>big_table</structname>.
親の問い合わせの制限をbig_table
のスキャンに直接適用することが出来ます。
An example where <literal>NOT MATERIALIZED</literal> could be
undesirable is
NOT MATERIALIZED
が望ましくない例を次に示します。
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 <literal>WITH</literal> query ensures
that <function>very_expensive_function</function> is evaluated only
once per table row, not twice.
ここで、WITH
問い合わせを生成すると、very_expensive_function
がテーブルの行毎に1回のみ評価され、2回は評価されないことが保証されます。
The examples above only show <literal>WITH</literal> being used with
<command>SELECT</command>, but it can be attached in the same way to
<command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, or <command>MERGE</command>.
In each case it effectively provides temporary table(s) that can
be referred to in the main command.
上の例ではSELECT
を使用するWITH
のみを示しています。
しかし、同じ方法でINSERT
、UPDATE
、DELETE
またはMERGE
に対して付与することができます。
それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。
WITH
内のデータ変更文 #
You can use data-modifying statements (<command>INSERT</command>,
<command>UPDATE</command>, <command>DELETE</command>, or
<command>MERGE</command>) in <literal>WITH</literal>. This
allows you to perform several different operations in the same query.
An example is:
《マッチ度[86.842105]》ほとんどのデータ変更文(INSERT
、UPDATE
、DELETE
は使用できますが、MERGE
は使用できません)は、WITH
内で使用できます。
これにより同じ問い合わせ内で複数の異なる操作を行うことができます。
《機械翻訳》WITH
では、データ-変更ステートメント(INSERT
、UPDATE
、DELETE
、またはMERGE
)を使用できます。
これにより、同じクエリで複数の異なる操作を実行できます。
以下に例を示します。
WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
This query effectively moves rows from <structname>products</structname> to
<structname>products_log</structname>. The <command>DELETE</command> in <literal>WITH</literal>
deletes the specified rows from <structname>products</structname>, returning their
contents by means of its <literal>RETURNING</literal> clause; and then the
primary query reads that output and inserts it into
<structname>products_log</structname>.
この問い合わせは実質、products
からproducts_log
に行を移動します。
WITH
内のDELETE
はproducts
から指定した行を削除し、そのRETURNING
句により削除した内容を返します。
その後、主問い合わせはその出力を読み取り、それをproducts_log
に挿入します。
A fine point of the above example is that the <literal>WITH</literal> clause is
attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within
the <command>INSERT</command>. This is necessary because data-modifying
statements are only allowed in <literal>WITH</literal> clauses that are attached
to the top-level statement. However, normal <literal>WITH</literal> visibility
rules apply, so it is possible to refer to the <literal>WITH</literal>
statement's output from the sub-<command>SELECT</command>.
上の例の見事なところは、WITH
句がINSERT
内のsub-SELECT
ではなく、INSERT
に付与されていることです。
これは、データ更新文は最上位レベルの文に付与されるWITH
句内でのみ許されているため必要です。
しかし、通常のWITH
の可視性規則が適用されますので、sub-SELECT
からWITH
文の出力を参照することができます。
Data-modifying statements in <literal>WITH</literal> usually have
<literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>),
as shown in the example above.
It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> the
target table of the data-modifying statement, that forms the temporary
table that can be referred to by the rest of the query. If a
data-modifying statement in <literal>WITH</literal> lacks a <literal>RETURNING</literal>
clause, then it forms no temporary table and cannot be referred to in
the rest of the query. Such a statement will be executed nonetheless.
A not-particularly-useful example is:
上の例で示したように、WITH
内のデータ変更文は通常RETURNING
句(6.4を参照)を持ちます。
問い合わせの残りの部分で参照することができる一時テーブルを形成するのは、RETURNING
句の出力の出力であって、データ変更文の対象テーブルではありません。
WITH
内のデータ変更文がRETURNING
句を持たない場合、一時テーブルを形成しませんので、問い合わせの残りの部分で参照することができません。
これにもかかわらずこうした文は実行されます。
特別有用でもない例を以下に示します。
WITH t AS ( DELETE FROM foo ) DELETE FROM bar;
This example would remove all rows from tables <structname>foo</structname> and
<structname>bar</structname>. The number of affected rows reported to the client
would only include rows removed from <structname>bar</structname>.
この例はfoo
テーブルとbar
テーブルからすべての行を削除します。
クライアントに報告される影響を受けた行数にはbar
から削除された行のみが含まれます。
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</literal>, for example:
データ変更文内の再帰的な自己参照は許されません。
一部の場合において、再帰的なWITH
の出力を参照することで、この制限を回避することができます。
以下に例を示します。
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);
This query would remove all direct and indirect subparts of a product. この問い合わせはある製品の直接的な部品と間接的な部品をすべて削除します。
Data-modifying statements in <literal>WITH</literal> are executed exactly once,
and always to completion, independently of whether the primary query
reads all (or indeed any) of their output. Notice that this is different
from the rule for <command>SELECT</command> in <literal>WITH</literal>: as stated in the
previous section, execution of a <command>SELECT</command> is carried only as far
as the primary query demands its output.
WITH
内のデータ変更文は正確に1回のみ実行され、主問い合わせがその出力をすべて(実際にはいずれか)を呼び出したかどうかに関係なく、常に完了します。
これが、前節で説明した主問い合わせがその出力を要求した時のみにSELECT
の実行が行われるというWITH
内のSELECT
についての規則と異なることに注意してください。
The sub-statements in <literal>WITH</literal> are executed concurrently with
each other and with the main query. Therefore, when using data-modifying
statements in <literal>WITH</literal>, the order in which the specified updates
actually happen is unpredictable. All the statements are executed with
the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they
cannot <quote>see</quote> one another's effects on the target tables. This
alleviates the effects of the unpredictability of the actual order of row
updates, and means that <literal>RETURNING</literal> data is the only way to
communicate changes between different <literal>WITH</literal> sub-statements and
the main query. An example of this is that in
WITH
内の副文はそれぞれと主問い合わせで同時に実行されます。
したがってWITH
内のデータ変更文を使用する時、指定したデータ変更文が実際に実行される順序は予測できません。
すべての文は同じスナップショット(第13章参照)を用いて実行されます。
このため互いが対象テーブルに行った影響を「見る」ことはできません。これは、行の更新に関する実際の順序が予測できないという影響を軽減し、RETURNING
データが別のWITH
副文と主問い合わせとの間で変更を伝える唯一の手段であることを意味します。
この例を以下に示します。
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products;
the outer <command>SELECT</command> would return the original prices before the
action of the <command>UPDATE</command>, while in
外側のSELECT
はUPDATE
の動作前の元々の価格を返します。
WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM t;
the outer <command>SELECT</command> would return the updated data.
一方こちらでは外側のSELECT
は更新されたデータを返します。
Trying to update the same row twice in a single statement 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 statement:
only the update is performed. Therefore you should generally avoid trying
to modify a single row twice in a single statement. In particular avoid
writing <literal>WITH</literal> sub-statements that could affect the same rows
changed by the main statement or a sibling sub-statement. The effects
of such a statement will not be predictable.
単一の文で同じ行を2回更新しようとすることはサポートされていません。
変更のうちの1つだけが行われますが、どれが実行されるかを確実に予測することは簡単ではありません(場合によっては不可能です)。
これはまた、同じ文内ですでに更新された行を削除する場合でも当てはまり、更新のみが実行されます。
したがって一般的には単一の文で1つの行を2回変更しようと試みることを避けなければなりません。
具体的には主文または同レベルの副文で変更される行と同じ行に影響を与えるWITH
副文を記述することは避けてください。
こうした文の影響は予測することはできません。
At present, any table used as the target of a data-modifying statement in
<literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal>
rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements.
現状、WITH
内のデータ変更文の対象として使用されるテーブルはすべて、複数の文に展開される条件付きルール、ALSO
ルール、INSTEAD
ルールを持ってはなりません。