問い合わせ全体を一度に実行するのではなく、カーソルを設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行ずつ読み取ることができます。
これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。
(しかし、PL/pgSQLユーザは通常これを心配する必要はありません。
FOR
ループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです。)
より興味深い使用方法として、呼び出し元が行を読み取ることをできるように、作成されたカーソルへの参照を返す方法があります。
これにより、関数から大量の行集合を返す際の効率が向上します。
PL/pgSQLにおけるカーソルへのアクセスは全て、カーソル変数を経由します。
カーソル変数は、常に特殊なrefcursor
データ型です。
カーソル変数を作成する1つの方法は、単にrefcursor
型の変数として宣言することです。
他の方法は、カーソル宣言構文を使用することです。
以下にその一般形を示します。
name
[ [ NO ] SCROLL ] CURSOR [ (arguments
) ] FORquery
;
(Oracleとの互換性のため、FOR
はIS
に置き換えることができます。)
もし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
の実装は、問い合わせの出力を再読み込みすると一貫した結果が返えることを仮定していて、これは揮発性の関数ではそうではありません。
カーソルを使用して行を取り出す前に、カーソルは開かれる必要があります。
(これはDECLARE CURSOR
SQLコマンドの動作と同じです。)
PL/pgSQLには3種類のOPEN
文があり、そのうちの2つはバインドされていないカーソル変数を使用し、残りの1つはバインドされたカーソル変数を使用します。
バインドされたカーソル変数は43.7.4で説明されているFOR
文で、明示的にカーソルを開かなくても使用できます。
FOR
ループはカーソルを開き、ループが完了すると再び閉じます。
カーソルを開くと、ポータルと呼ばれるサーバ内部のデータ構造が作成されます。ポータルは、カーソルの問い合わせの実行状態を保持します。 ポータルには名前があり、ポータルの存続期間中はセッション内で一意でなければなりません。 デフォルトでは、PL/pgSQLは作成する各ポータルに一意の名前を割り当てます。 しかし、カーソル変数にNULL文字列以外の値を割り当てると、その文字列がポータル名として使用されます。 この機能は43.7.3.5で説明するように使うことができます。
OPEN FOR
query
#OPENunbound_cursorvar
[ [ NO ] SCROLL ] FORquery
;
カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。
既に開いたカーソルを開くことはできず、また、バインドされていないカーソル変数として(つまり、単なるrefcursor
変数として)宣言されていなければなりません。
この問い合わせはSELECT
文であるか、または(EXPLAIN
のように)何らかの行を返すものでなければなりません。
この問い合わせは、他のPL/pgSQLのSQL文と同様の方法で扱われます。
PL/pgSQLの変数名は置き換えられ、問い合わせ計画は再利用できるようにキャッシュされます。
PL/pgSQL変数がカーソルを使用する問い合わせに代入された時、変数はOPEN
時の値となり、その後の変更はカーソルの動きに影響しません。
SCROLL
およびNO SCROLL
オプションの意味はバインドされたカーソルと同様です。
以下に例を示します。
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN FOR EXECUTE
#OPENunbound_cursorvar
[ [ NO ] SCROLL ] FOR EXECUTEquery_string
[ USINGexpression
[, ... ] ];
カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。
既に開いたカーソルを開くことはできず、また、バインドされていないカーソル変数として(つまり、単なる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
経由で埋め込まれますので、引用符を付ける必要がありません。
OPENbound_cursorvar
[ ( [argument_name
:= ]argument_value
[, ...] ) ];
宣言時に問い合わせが結び付いたカーソル変数を開くために使用されるOPEN
の形式です。
既に開いたカーソルを開くことはできません。
実引数の式のリストはカーソルが引数を取るものと宣言された場合にのみ現れます。
これらの値は問い合わせの中で置き換えられます。
バインドされたカーソルの問い合わせ計画は常にキャッシュ可能とみなされます。
この場合、EXECUTE
と等価なものはありません。
SCROLL
およびNO SCROLL
をOPEN
において指定できないことに注意してください。
カーソル移動の仕様はすでに決まっているからです。
位置的表記または記名的表記を使用して、引数の値を渡すことができます。
位置的表記では、全ての引数が順番に指定されます。
記名的表記では、引数の式と区別するために:=
を使用して、各々の引数の名前が指定されます。
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;
カーソルを開いてから、ここで説明する文を使用してカーソルを扱うことができます。
これらの操作は、カーソルを開始するために開いた関数内で行う必要はありません。
関数からrefcursor
値を返し、呼び出し元でそのカーソルの操作をさせることもできます。
(内部的にはrefcursor
値は、カーソルへの有効な問い合わせを持つポータルの名前を示す単なる文字列です。
この名前は、ポータルを壊すことなく、他のrefcursor
型の変数に渡したり、代入したりすることなどができます。)
全てのポータルは、暗黙的にトランザクションの終わりで閉ざされます。
したがって、refcursor
値はそのトランザクションの終わりまでの間のみ開いたカーソルへの参照として有効です。
FETCH
#FETCH [direction
{ FROM | IN } ]cursor
INTOtarget
;
FETCH
はSELECT INTO
と同様に、カーソルから次の行を(指定された方向に)抽出し、対象に格納します。
対象とは、行変数、レコード変数、または単純な変数をカンマで区切ったリストです。
適切な行がない場合、ターゲットはNULLに設定されます。
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;
MOVE
#MOVE [direction
{ FROM | IN } ]cursor
;
MOVE
コマンドは、データを取り出さないでカーソルの位置を変更します。
MOVE
コマンドは、移動先の行を返さないでカーソルの位置だけを変更することを除けば、FETCH
コマンドと同様の働きをします。
direction
句は、SQL FETCHコマンドで許可されるどのような形式も可能で、複数行を取り出すことができるものも含まれます。
カーソルはそのような行の最後に位置します。
(しかし、direction
句が単にキーワードのないcount
式である場合は、PL/pgSQLでは非推奨です。
この構文は、direction
句がすべて省略された場合との区別が曖昧であるため、count
が定数でない場合は失敗する可能性があります。)
SELECT INTO
と同様に、特殊な変数FOUND
を用いて、移動先に行が存在するかどうかを検査できます。
そのような行がない場合、カーソルは移動方向に応じて最後の行の後または最初の行の前に位置します。
例:
MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;
UPDATE/DELETE WHERE CURRENT OF
#UPDATEtable
SET ... WHERE CURRENT OFcursor
; DELETE FROMtable
WHERE CURRENT OFcursor
;
カーソルの位置をテーブルの行に変更すれば、カーソルによって特定した行を更新または消去できます。
カーソル問い合わせは何が許されているのか(特にグループ化しないとき)についての制限があり、それはカーソル内でFOR UPDATE
を使用することが最善です。
より詳細についてはDECLAREマニュアルページを参照下さい。
以下に例を示します。
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
CLOSE
#
CLOSE cursor
;
CLOSE
は、開いているカーソルの背後にあるポータルを閉じます。
これを使用してトランザクションの終わりよりも前にリソースを解放することができ、また、カーソル変数を解放し、再度開くことができます。
例:
CLOSE curs1;
PL/pgSQL関数では、呼び出し元にカーソルを返すことができます。 この方法は、関数から複数行または複数列を返す場合、特にその結果集合が非常に大きい場合に有用です。 これを行うには、関数はカーソルを開き、呼び出し元にカーソル名を返します(もしくは、もし呼び出し元でポータル名がわかっていれば、単純に指定されたポータル名を使用してカーソルを開きます)。 これにより、呼び出し元はカーソルから行を取り出すことができるようになります。 カーソルは呼び出し元で閉じることができます。 または、トランザクションが終了した際に自動的に閉じられます。
カーソル用のポータル名は、プログラマが指定するか、または自動的に生成されます。
ポータル名を指定するには、開く前に、単にrefcursor
変数に文字列を代入します。
refcursor
変数の文字列値はOPEN
によって、背後のポータル名として使用されます。
しかし、refcursor
変数の値がNULLの場合(デフォルトではそうなっています)、OPEN
は自動的に既存のポータルと競合しない名前を生成し、それをrefcursor
変数に代入します。
PostgreSQL 16以前は、バインドされたカーソル変数は、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;
カーソルで返される行に対して反復することができるFOR
文の亜種があります。
構文は以下のようになります。
[ <<label
>> ] FORrecordvar
INbound_cursorvar
[ ( [argument_name
:= ]argument_value
[, ...] ) ] LOOPstatements
END LOOP [label
];
カーソル変数は宣言されたとき、何らかの問い合わせと結び付けられて(バインドされて)いなければならず、また既に開かれていてはなりません。
FOR
文は自動的にカーソルを開き、ループから抜けたときに再度閉じます。
実際の引数値式のリストは、カーソルが引数を取ることを宣言された場合に限ってのみ出現できます。
これらの値は、OPEN
過程と同じ方法で、問い合わせの中で置換されます(43.7.2.3を参照してください)。
recordvar
変数は、record
型として自動的に定義され、ループ内でのみ存在します
(存在するいかなる変数名の定義もループ内では無視されます)。
カーソルによって返されたそれぞれの行はこのレコード変数に引き続いて割り当てられ、ループ本体が実行されます。