★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

43.7. カーソル

問い合わせ全体を一度に実行するのではなく、カーソルを設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行ずつ読み取ることができます。 これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。 (しかし、PL/pgSQLユーザは通常これを心配する必要はありません。 FORループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです。) より興味深い使用方法として、呼び出し元が行を読み取ることをできるように、作成されたカーソルへの参照を返す方法があります。 これにより、関数から大量の行集合を返す際の効率が向上します。

43.7.1. カーソル変数の宣言

PL/pgSQLにおけるカーソルへのアクセスは全て、カーソル変数を経由します。 カーソル変数は、常に特殊なrefcursorデータ型です。 カーソル変数を作成する1つの方法は、単にrefcursor型の変数として宣言することです。 他の方法は、カーソル宣言構文を使用することです。 以下にその一般形を示します。

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

Oracleとの互換性のため、FORISに置き換えることができます。) もしSCROLLを指定すれば、カーソルは逆方向に移動できます。 もしNO SCROLLを指定すれば、逆方向の行の取り出しはできません。 どちらも指定しない時、逆方向に取り出しできるかは問い合わせに依存します。 もしargumentsがあれば、name datatypeをカンマで区切ったリストで、与えられた問い合わせ内のパラメータ値として置換される名前を定義します。 その名前に実際に置換される値は、カーソルを開いた時点より後に指定されます。

以下に例を示します。

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

これら3つの変数は全てrefcursorデータ型を持ちますが、最初のものは全ての問い合わせに使用でき、2番目には完全な問い合わせが既にバウンドされています(結び付けられています)。 また、最後のものには、パラメータ付きの問い合わせがバウンドされています (keyはカーソルが開いた時に整数パラメータ値に置き換えられます)。 curs1変数は、特定の問い合わせに結び付けられていませんので、アンバウンドであると呼ばれます。

カーソルの問い合わせがFOR UPDATE/SHAREを使っている場合、SCROLLオプションは使えません。 また、揮発性の関数を伴う問い合わせにはNO SCROLLを使うことが最善です。 SCROLLの実装は、問い合わせの出力を再読み込みすると一貫した結果が返えることを仮定していて、これは揮発性の関数ではそうではありません。

43.7.2. カーソルを開く

カーソルを使用して行を取り出す前に、開かれる必要があります。 (これはDECLARE CURSOR SQLコマンドの動作と同じです。) PL/pgSQLには3種類のOPEN文があり、そのうちの2つはアンバウンドカーソル変数を使用し、残りの1つはバウンドカーソル変数を使用します。

注記

バウンドカーソル変数は43.7.4で説明されているFOR文で、明示的にカーソルを開かなくても使用することができます。

43.7.2.1. OPEN FOR query

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。 既に開いたカーソルを開くことはできず、また、アンバウンドカーソル変数として(つまり、単なるrefcursor変数として)宣言されていなければなりません。 この問い合わせはSELECT文であるか、または(EXPLAINのように)何らかの行を返すものでなければなりません。 この問い合わせは、他のPL/pgSQLのSQL文と同様の方法で扱われます。 PL/pgSQLの変数名は置き換えられ、問い合わせ計画は再利用できるようにキャッシュされます。 PL/pgSQL変数がカーソルを使用する問い合わせに代入された時、変数はOPEN時の値となり、その後の変更はカーソルの動きに影響しません。 SCROLLおよびNO SCROLLオプションの意味はバウンドカーソルと同様です。

以下に例を示します。

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

43.7.2.2. OPEN FOR EXECUTE

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                     [ USING expression [, ... ] ];

カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。 既に開いたカーソルを開くことはできず、また、アンバウンドカーソル変数として(つまり、単なるrefcursor変数として)宣言されていなければなりません。 問い合わせは、EXECUTEコマンドと同じ方法による文字列式として指定されます。 通常と同様に、これにより、次回に実行する際に違った問い合わせを計画できる柔軟性が得られます(43.11.2参照)。 また、変数置換がコマンド文字列上で行われないことも意味します。 EXECUTEと同様にformat()USINGを介して動的コマンドにパラメータ値を挿入することができます。 SCROLLおよびNO SCROLLオプションの意味はバウンドカーソルと同様です。

以下に例を示します。

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

この例では、テーブル名は問い合わせにformat()で挿入されています。 col1との比較値はUSING経由で埋め込まれますので、引用符を付ける必要がありません。

43.7.2.3. バウンドカーソルを開く

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

宣言時に問い合わせが結び付いたカーソル変数を開くために使用されるOPENの形式です。 既に開いたカーソルを開くことはできません。 実引数の式のリストはカーソルが引数を取るものと宣言された場合にのみ現れます。 これらの値は問い合わせの中で置き換えられます。

バウンドカーソルの問い合わせ計画は常にキャッシュ可能とみなされます。 この場合、EXECUTEと等価なものはありません。 SCROLLおよびNO SCROLLOPENにおいて指定できないことに注意してください。 カーソル移動の仕様はすでに決まっているからです。

位置的表記または記名的表記を使用して、引数の値を渡すことができます。 位置的表記では、全ての引数が順番に指定されます。 記名的表記では、引数の式と区別するために:=を使用して、各々の引数の名前が指定されます。 4.3に記述した関数呼び出しと同様に、位置的表記と記名的表記を混用できます。

例を示します(ここでは上例のカーソル宣言を使用します)。

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

変数の代入はバウンドカーソルの問い合わせで行われるため、カーソルへ値を渡す方法が2つあります。 OPENコマンドの明確な引数とするものと、問い合わせにおけるPL/pgSQL変数として暗黙的に参照するものです。 しかし、バウンドカーソルの宣言より前に宣言した変数だけが代入されます。 どちらの場合も、OPENの実行時に変数値が決まります。 例えば、上例のcurs3と同じ結果を取得する方法を、以下に示します。

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

43.7.3. カーソルの使用

カーソルを開いてから、ここで説明する文を使用してカーソルを扱うことができます。

これらの操作は、カーソルを開始するために開いた関数内で行う必要はありません。 関数からrefcursor値を返し、呼び出し元でそのカーソルの操作をさせることもできます。 (内部的にはrefcursor値は、カーソルへの有効な問い合わせを持つポータルの名前を示す単なる文字列です。 この名前は、ポータルを壊すことなく、他のrefcursor型の変数に代入することで、他に渡すことができます。)

全てのポータルは、暗黙的にトランザクションの終わりで閉ざされます。 したがって、refcursor値はそのトランザクションの終わりまでの間のみ開いたカーソルへの参照として有効です。

43.7.3.1. FETCH

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCHSELECT INTOと同様に、カーソルから次の行を抽出し、対象に格納します。 対象とは、行変数、レコード変数、または単純な変数をカンマで区切ったリストです。 SELECT INTOの場合と同様、特殊なFOUND変数を検査することで、行が取得できたかどうかを確認することができます。

direction句は複数行を取り出すことができるコマンドを除き、SQL FETCHで許可されたどのようなコマンドも可能です。 すなわち、以下のものです。 NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARDまたは BACKWARD. direction句の省略は、NEXTの指定と同じです。 countを使う形式では、countはいかなる整数値の式も可能です。(SQL FETCHコマンドとは異なります。あちらは整数定数のみを受け付けます。) SCROLLオプションを用いてカーソルを宣言または開かないと、directionの値による逆方向への移動の要求は失敗します。

cursor名は、開いているカーソルのポータルを参照するrefcursor変数名でなければなりません。

例:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

43.7.3.2. MOVE

MOVE [ direction { FROM | IN } ] cursor;

MOVEコマンドは、データを取り出さないでカーソルの位置を変更します。 MOVEコマンドは、移動先の行を返さないでカーソルの位置だけを変更することを除けば、FETCHコマンドと同一の働きをします。 SELECT INTOと同様に、特殊な変数FOUNDを用いて、移動先に行が存在するかどうかを検査できます。

例:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

43.7.3.3. UPDATE/DELETE WHERE CURRENT OF

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

カーソルの位置をテーブルの行に変更すれば、カーソルによって特定した行を更新または消去できます。 カーソル問い合わせは何が許されているのか(特にグループ化しないとき)についての制限があり、それはカーソル内でFOR UPDATEを使用することが最善です。 より詳細についてはDECLAREマニュアルページを参照下さい。

以下に例を示します。

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

43.7.3.4. CLOSE

CLOSE cursor;

CLOSEはポータルの背後にあるカーソルを閉じます。 これを使用してトランザクションの終わりよりも前にリソースを解放することができ、また、カーソル変数を解放し、再度開くことができます。

例:

CLOSE curs1;

43.7.3.5. カーソルを返す

PL/pgSQL関数では、呼び出し元にカーソルを返すことができます。 この方法は、関数から複数行または複数列を返す場合、特にその結果集合が非常に大きい場合に有用です。 これを行うには、関数はカーソルを開き、呼び出し元にカーソル名を返します(もしくは、もし呼び出し元でポータル名がわかっていれば、単純に指定されたポータル名を使用してカーソルを開きます)。 これにより、呼び出し元はカーソルから行を取り出すことができるようになります。 カーソルは呼び出し元で閉じることができます。 または、トランザクションが終了した際に自動的に閉じられます。

カーソル用のポータル名は、プログラマが指定するか、または自動的に生成されます。 ポータル名を指定するには、開く前に、単にrefcursor変数に文字列を代入します。 refcursor変数の文字列値はOPENによって、背後のポータル名として使用されます。 しかし、refcursor変数がNULLの場合、OPENは自動的に既存のポータルと競合しない名前を生成し、それをrefcursor変数に代入します。

注記

バウンドカーソル変数は、その名前を表現する文字列値で初期化されます。 そのため、プログラマがカーソルを開く前に代入により上書きしない限り、ポータル名はカーソル変数と同じになります。 しかし、アンバウンドカーソル変数の初期値はデフォルトでNULLです。 そのため、上書きされていない場合に自動的に生成される一意な名前を受け取ります。

以下の例は、呼び出し元でカーソル名を指定する方法を示しています。

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

以下の例では、自動的に生成されたカーソル名を使用しています。

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;


-- カーソルを使用するには、トランザクション内部である必要があります。
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

以下の例は単一関数から複数のカーソルを返す方法を示しています。

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;


-- カーソルを使用するには、トランザクション内部である必要があります。
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

43.7.4. カーソル結果に対するループ

カーソルで返される行に対して反復することができるFOR文の亜種があります。 構文は以下のようになります。

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

カーソル変数は宣言されたとき、何らかの問い合わせとバウンドされていなければならず、また既に開かれていてはなりませんFOR文は自動的にカーソルを開き、ループから抜けたときに再度閉じます。 実際の引数値式のリストは、カーソルが引数を取ることを宣言された場合に限ってのみ出現できます。 これらの値は、OPEN過程と同じ方法で、問い合わせの中で置換されます(43.7.2.3を参照してください)。

recordvar変数は、record型として自動的に定義され、ループ内でのみ存在します (存在するいかなる変数名の定義もループ内では無視されます)。 カーソルによって返されたそれぞれの行はこのレコード変数に引き続いて割り当てられ、ループ本体が実行されます。