問い合わせ全体を一度に実行するのではなく、カーソル を設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行づつ読みとることができます。これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです(しかし、PL/pgSQL ユーザは通常これを心配する必要はありません。FOR ループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです)。より興味深い可能性は、関数が、呼び出し元が行を読みとることができるように設定するカーソルの参照を返すことができることです。これは、関数から行集合を返す方法の1つを提供します。
PL/pgSQL における全てのカーソルへのアクセスはカーソル変数を経由します。カーソル変数は常に特殊な refcursor データ型です。カーソル変数を作成する1つの方法は、単に refcursor 型の変数として宣言することです。他の方法は、カーソル宣言構文を使用することです。これは一般的には以下のようなものです(Oracle との互換性のため、FOR は IS に置き換えることができます)。
name CURSOR [ ( arguments ) ] FOR select_query ;
arguments はもしあれば、 name と datatype の組み合わせをコンマで区切ったリストで、与えられた問い合わせ内のパラメータ値として置換される名前を定義します。その名前に実際に代入される値は、カーソルを開いた時点より後に指定されます。
以下に例を示します。
DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * from tenk1; curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key;
これら3つの変数はすべて refcursor データ型を持ちますが、最初のものは全ての問い合わせに使用でき、2番目は完全な問い合わせが既に バウンドされて(結びつけられて) います。また、最後のものは、パラメータ付きの問い合わせが結びつけられています(key はカーソルが開いた時に整数パラメータ値に置き換えられます)。 curs1 変数は、特定の問い合わせに結びつけられていませんので、アンバウンドであると呼ばれます。
カーソルを使用して行を取り出す前に、開かれる必要があります(これは DECLARE CURSOR SQLコマンドの動作と同じです)。PL/pgSQL には 4 種類の OPEN 文があり、内2つはアンバウンドカーソル変数を使用、残り2つはバウンドカーソル変数を使用するためのものです。
OPEN unbound-cursor FOR SELECT ...;
カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。既に開いたカーソルを開くことはできず、また、アンバウンドカーソルとして(つまり、単なる refcursor 変数として)宣言されていなければなりません。SELECT 問い合わせは、他の PL/pgSQL の SELECT と同様の方法で扱われます。PL/pgSQL の変数名は置き換えられ、問い合わせ計画は再利用できるようにキャッシュされます。
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound-cursor FOR EXECUTE query-string;
カーソル変数は開かれ、実行するよう指定した問い合わせが付与されます。既に開いたカーソルを開くことはできず、また、アンバウンドカーソルとして(つまり、単なる refcursor 変数として)宣言されていなければなりません。問い合わせは、EXECUTE コマンドと同じ方法による文字列式として指定されます。通常、これは、次回に実行する問い合わせが変化する問い合わせを行う場合に柔軟性を与えます。
OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
OPEN bound-cursor [ ( argument_values ) ];
宣言時に問い合わせが結びつけられるカーソル変数を開くために使用される OPEN の形式です。既に開いたカーソルを開くことはできません。実引数の評価式はカーソルが引数をとるものと宣言された場合にのみ現れます。この値は問い合わせの中に置き換えられます。バウンドカーソルの問い合わせ計画は常にキャッシュ可能とみなされます。この場合と等価な EXECUTE はありません。
OPEN curs2; OPEN curs3(42);
カーソルを開いてから、ここで説明する文を使用してカーソルを扱うことができます。
これらの操作は、カーソルを開始するために開いたその関数内で行う必要はありません。関数から refcursor 値を返し、呼び出し元でそのカーソルの操作をさせることもできます(内部的にはrefcursor値は、カーソルへの有効な問い合わせを持つポータル(入口)の名前を示す単なる文字列です。この名前は、ポータルを壊すことなく、他のrefcursor型の変数に代入することで、他に渡すことができます)。
全てのポータルは暗黙的にトランザクションの終りで閉ざされます。従って、refcursor 値はそのトランザクションの終りまでの間のみ開いたカーソルへの参照として有効です。
FETCH cursor INTO target;
FETCH はカーソルから次の行を取り出し、SELECT INTO のように対象に格納します。対象とは、行変数、またはレコード変数、あるいは、単純な変数をコンマで区切ったリストです。SELECT INTO の場合と同様、特殊な FOUND 変数を検査することで、行が入手できたかどうかを確認することができます。
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo,bar,baz;
CLOSE cursor;
CLOSE はポータルの背後のカーソルを閉じます。これを使用してトランザクションの終りよりも前にリソースを解放することができ、また、カーソル変数を解放し、再度開くことができます。
CLOSE curs1;