DECLARE — カーソルを定義する
DECLAREname
[ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FORquery
DECLARE
を使うと、カーソルが使用できるようになります。
これは、巨大な問い合わせの結果から一度に少数の行を取り出す機能です。
カーソルを作成した後、FETCHを使用して行を取り出します。
このマニュアルページではSQLコマンドレベルでのカーソルの使用方法について説明します。 PL/pgSQL内でカーソルを使用するつもりであれば、規則が異なりますので、41.7. カーソルを参照してください。
name
作成されるカーソルの名前です。
BINARY
カーソルによるデータの取得が、テキスト形式ではなくバイナリ形式になります。
INSENSITIVE
カーソルから取り出されたデータが、カーソルを作成した後に行われた背後にあるテーブルの更新の影響を受けないことを示します。 これはPostgreSQLのデフォルトの動作ですので、このキーワードを使用しても効果はなく、このキーワードは標準SQLとの互換性を保持するために存在しています。
SCROLL
NO SCROLL
SCROLL
は、そのカーソルから通常の順序通りでない方法で(例えば後方から)行を取り出し可能であることを指定します。
問い合わせの実行計画が複雑になると、SCROLL
の指定によって問い合わせの実行時間が増大する可能性があります。
NO SCROLL
は、そのカーソルから順序通りでない方法では行を取り出せないことを指定します。
デフォルトでは、いくつかの場合でスクロール可能です。
これはSCROLL
の指定と同じではありません。
詳細は注釈を参照してください。
WITH HOLD
WITHOUT HOLD
WITH HOLD
は、カーソルを生成したトランザクションが正常にコミット処理を行った後も、そのカーソルの使用を続けられることを指定します。
WITHOUT HOLD
は、カーソルを生成したトランザクションの外部では、そのカーソルを使用できないことを指定します。
WITH HOLD
もWITHOUT HOLD
も指定されない場合、WITHOUT HOLD
がデフォルトとなります。
query
BINARY
、INSENSITIVE
、SCROLL
キーワードは任意の順番で指定することができます。
通常のカーソルは、SELECT
の出力と同じテキスト形式でデータを返します。
BINARY
は、カーソルがバイナリ形式でデータを返すことを示します。
これによりサーバ、クライアントの両方で変換に関する作業を省くことができますが、プラットフォームに依存するバイナリデータ書式を扱うためのプログラマの作業が大きくなります。
例えば、問い合わせが整数の列から値として1を返す場合、デフォルトのカーソルからは1
という文字列を取得することになりますが、バイナリ形式のカーソルからは、内部表現を使った4バイトの値を(ビッグエンディアンのバイト順で)取得することになります。
バイナリ形式のカーソルは注意して使わなければなりません。 psqlなどの多くのアプリケーションは、データはテキスト形式で返されることを期待しており、バイナリ形式のカーソルを扱うことができません。
クライアントアプリケーションが「拡張問い合わせ」プロトコルを使用してFETCH
コマンドを発行する場合、テキスト形式とバイナリ形式のどちらでデータを受け取るのかは、バインドプロトコルメッセージで指定します。
この選択は、カーソル定義での指定を上書きします。
全てのカーソルをテキスト形式/バイナリ形式のどちらでも扱うことができる拡張問い合わせプロトコルでは、バイナリカーソルという概念は旧式なものです。
WITH HOLD
が指定されなければ、このコマンドで生成されるカーソルは現在のトランザクションの中でのみ使用することができます。
したがって、WITH HOLD
のないDECLARE
はトランザクションブロックの外側では意味がありません。
その場合、カーソルはこの文が完了するまでのみ有効です。
そのため、PostgreSQLはトランザクションブロックの外部でこうしたコマンドが使用された場合エラーを報告します。
トランザクションブロックを定義するには、BEGINとCOMMIT(またはROLLBACK)を使用してください。
WITH HOLD
が指定され、カーソルを作成したトランザクションのコミットに成功した場合、同一セッション内のその後のトランザクションからそのカーソルにアクセスすることができます
(ただし、トランザクションがアボートされた場合、そのカーソルは削除されます)。
WITH HOLD
付きで作成されたカーソルは、そのカーソルに対して明示的なCLOSE
が発行された場合やセッションが終了した時に閉じられます。
現在の実装では、保持されたカーソルを使って表される行は、その後のトランザクションでも利用できるように、一時ファイルかメモリ領域にコピーされます。
問い合わせがFOR UPDATE
またはFOR SHARE
を含む場合、WITH HOLD
を指定することはできません。
カーソルから逆方向にデータを取り出す時には、SCROLL
オプションを指定するべきです。
これは標準SQLでは必須となっています。
しかし、以前のバージョンとの互換性を保持するために、PostgreSQLでは、カーソルの問い合わせ計画が単純であり、そのサポートに余計なオーバーヘッドが必要ない場合、 SCROLL
なしでも逆方向にデータを取り出すことができます。
しかし、SCROLL
を付けなくても逆方向にデータが取り出せることを利用してアプリケーションを開発するのはお勧めしません。
NO SCROLL
を指定した場合は、どのような場合でも逆方向に取り出すことはできません。
また、問い合わせがFOR UPDATE
またはFOR SHARE
を含む場合は、逆方向の取り出しは許されません。
このためこの場合はSCROLL
を指定することはできません。
スクロール可能なWITH HOLD
カーソルが揮発関数(36.6. 関数の変動性分類参照)を含む場合、想定しない結果をもたらす可能性があります。
これまで取り出した行を再度取り出した時、関数は再実行される可能性があり、この場合おそらく初回と異なる結果をもたらします。
こうした問題の回避方法の1つとして、カーソルをWITH HOLD
と宣言し、そこから何か行を読み取る前にトランザクションをコミットすることがあります。
これにより強制的にカーソルの出力全体が一時領域に具現化され、揮発関数は各行に対して1度しか実行されなくなります。
カーソルの問い合わせがFOR UPDATE
またはFOR SHARE
を含む場合、このオプションを持つ通常のSELECTコマンドと同様、返される行は取り出した時点でロックされます。
さらに、返される行はもっとも最新のバージョンになります。
したがって、このオプションは、標準SQLで「センシティブカーソル」と呼ばれるものと同じ機能を提供します。
(INSENSITIVE
をFOR UPDATE
またはFOR SHARE
といっしょに指定するとエラーになります。)
カーソルをUPDATE ... WHERE CURRENT OF
またはDELETE ... WHERE CURRENT OF
で使用するつもりならば、FOR UPDATE
の使用を通常勧めます。
FOR UPDATE
を使用することで、取り出してから更新されるまでの間に他のセッションが行を変更することを防止します。
FOR UPDATE
がなければ、カーソル作成後に行が変更された場合に後に行うWHERE CURRENT OF
コマンドは効果がなくなります。
FOR UPDATE
を使用する他の理由は、「簡単に更新可能」にするためにカーソル問い合わせが標準SQLに合わない場合(具体的にはカーソルは1つのテーブルのみを参照しなければならず、また、グループ化やORDER BY
を使用してはならない)、これがないと後に実行されるWHERE CURRENT OF
が失敗するかもしれないことです。
計画選択の詳細によっては、簡単に更新可能でないカーソルは動作するかもしれませんし、動作しないかもしれません。
このため最悪の場合、アプリケーションは試験時に動作するが、運用時に失敗するかもしれません。
FOR UPDATE
をWHERE CURRENT OF
といっしょに使用しない大きな理由は、カーソルをスクロール可能にする必要がある、または後の更新の影響を受けないようにする(つまり古いデータを表示し続けるようにする)必要がある場合のためです。
これが必要ならば、上記の警告に十分注意してください。
標準SQLでは、組み込みSQLにおけるカーソルのみが規定されています。
PostgreSQLサーバはカーソル用のOPEN
文を実装していません。
カーソルは宣言された時に開いたものとみなされています。
しかし、PostgreSQL用の埋め込みSQLプリプロセッサであるECPGでは、DECLARE
とOPEN
文などを含め、標準SQLのカーソル規定をサポートしています。
pg_cursors
システムビューを問い合わせることで、利用可能なすべてのカーソルを確認することができます。
標準SQLでは、デフォルトでカーソルが背後にあるデータの同時実行更新に影響を受けるかどうかは実装依存であると述べています。
PostgreSQLのカーソルはデフォルトでは影響を受けず、FOR UPDATE
を指定することで影響を受けることができます。
他の製品では異なる動作をするかもしれません。
標準SQLでは、カーソルを埋め込みSQL内とモジュール内でのみ使用できます。 PostgreSQLでは、対話式にカーソルを使うことができます。
バイナリカーソルはPostgreSQLの拡張です。