問い合わせ全体を一度に実行するのではなく、カーソルを設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行づつ読みとることができます。これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです(しかし、PL/pgSQL ユーザは通常これを心配する必要はありません。FOR ループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです)。 より興味深い使用方法として、呼び出し元が行を読み取ることのできるように、作成されたカーソルへの参照を返す方法があります。 これにより、関数から大量の行セットを返す際の効率が向上します。
PL/pgSQL における全てのカーソルへのアクセスは、カーソル変数を経由します。 カーソル変数は、常に特殊な refcursor データ型です。カーソル変数を作成する1つの方法は、単に refcursor 型の変数として宣言することです。他の方法は、カーソル宣言構文を使用することです。
name CURSOR [ ( arguments ) ] FOR select_query ;(これは一般的には以下のようなものです(Oracle との互換性のため、FOR は IS に置き換えることができます)。 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);
カーソルを開いてから、ここで説明する文を使用してカーソルを扱うことができます。
これらの操作は、カーソルを開始するために開いたその関数内で行う必要はありません。 関数から 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;
PL/pgSQL 関数では、呼び出し元にカーソルを返すことができます。 この方法は、関数から複数行または複数列を返す場合に使用します。 関数は、カーソルを開き、呼び出し元にカーソル名を返します。 これにより、呼び出し元は、カーソルから行を FETCH できるようになります。 カーソルは、呼び出し元で閉じることができます。または、トランザクションが終了した際に自動的に閉じられます。
関数によって返されるカーソル名は、呼び出し元で指定するか、または自動で生成されます。 以下の例は、呼び出し元でカーソル名を指定する方法を示しています。
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;