ブロック内で使用される全ての変数はそのブロックの宣言部で宣言されなければなりません。
(唯一の例外は、FOR
ループである整数値の範囲に渡って繰り返されるループ変数で、これは、自動的に整数型変数として宣言されます。
同様に、カーソルの結果に対して繰り返し適用されるFOR
ループのループ変数はレコード変数として自動的に宣言されます。)
PL/pgSQL変数は、integer
、varchar
、char
といった、任意のSQLデータ型を持つことができます。
変数宣言の例を以下に示します。
user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;
変数宣言の一般的な構文は以下の通りです。
name
[ CONSTANT ]type
[ COLLATEcollation_name
] [ NOT NULL ] [ { DEFAULT | := | = }expression
];
DEFAULT
句が指定された場合、ブロックに入った時に変数に代入される初期値を指定します。
DEFAULT
句が指定されない場合、変数はSQLのNULL値に初期化されます。
CONSTANT
オプションにより、そのブロック内でその値が不変になるように、その変数への初期化後の代入は禁止されます。
COLLATE
オプションは、変数として使用するための照合を指定します(43.3.6を参照してください)。
NOT NULL
が指定された場合、NULL値の代入は実行時エラーになります。
NOT NULL
として宣言した変数は全て、非NULLのデフォルト値を指定しなければなりません。
等号(=
)がPL/SQLにおける代入記号(:=
)の代わりに使用できます。
変数のデフォルト値はブロックに入る度に評価され、変数に代入されます(関数を呼び出す時に一度だけではありません)。
ですから、例えばnow()
をtimestamp
型の変数に代入することで、その変数には関数をプリコンパイルした時刻ではなく、関数呼び出し時の現在時刻が格納されます。
例:
quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; transaction_time CONSTANT timestamp with time zone := now();
宣言された変数の値は、同じブロック内の後の初期化式で使用できます。 次に例を示します。
DECLARE x integer := 1; y integer := x + 1;
関数に渡されるパラメータの名前には$1
、$2
という識別子が付けられます。
省略することもできますが、$
というパラメータ名に別名を宣言することができ、可読性が向上します。
別名、数字による識別子の両方とも引数の値を参照する時に使用することができます。
n
別名を作成する方法は2つあり、望ましい方法はCREATE FUNCTION
コマンドの中でパラメータを命名するものです。
以下に例を示します。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
他の方法は、宣言構文を用いて別名を明確に宣言するものです。
name
ALIAS FOR $n
;
以下にこの方法による例を示します。
CREATE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
この二例は完全に同等ではありません。
最初の例では、subtotal
をsales_tax.subtotal
で参照できますが、次の例ではできません
(その代わり、内部ブロックにラベルを付与すれば、subtotal
をラベルで修飾することができます)。
さらに数例を示します。
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- v_string とインデックスを使用した何らかの演算を行なう END; $$ LANGUAGE plpgsql; CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql;
PL/pgSQL関数が出力パラメータと共に宣言されると、通常の入力パラメータと同様に、出力パラメータには$
というパラメータ名と任意の別名が与えられます。
出力パラメータは実質的には最初がNULL値の変数であり、関数の実行中に値が指定されるはずです。
出力パラメータの最後の値は戻り値です。
例えば、消費税の例題は、次のようにすることもできます。
n
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; END; $$ LANGUAGE plpgsql;
RETURNS real
を省略したことに注意してください。
含めることもできますが、冗長になります。
OUT
パラメータの付いた関数を呼び出すには、関数呼び出しで出力パラメータを省略してください。
SELECT sales_tax(100.00);
出力パラメータは複数の値を返す時に最も有用になります。 簡単な例題を示します。
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; SELECT * FROM sum_n_product(2, 4); sum | prod -----+------ 6 | 8
38.5.4で述べたように、この方法は関数の結果に対する匿名のレコード型を実質的に作成します。
RETURNS
句が与えられた時は、RETURNS record
と言わなければなりません。
これは以下のようにプロシージャでも機能します。
CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql;
プロシージャの呼び出しでは、すべてのパラメータを指定しなければなりません。
普通のSQLからプロシージャを呼び出す場合には、出力パラメータに対してはNULL
を指定します。
CALL sum_n_product(2, 4, NULL, NULL); sum | prod -----+------ 6 | 8
しかしながら、PL/pgSQLからプロシージャを呼び出すときには、出力パラメータに対して変数を書かないといけません。変数は呼び出しの結果を受け取ります。 詳細は43.6.3を参照してください。
PL/pgSQL関数を宣言する他の方法として、RETURNS TABLE
を伴うことが挙げられます。
以下に例を示します。
CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$ BEGIN RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s WHERE s.itemno = p_itemno; END; $$ LANGUAGE plpgsql;
これは、1つ、またはそれ以上のOUT
パラメータを宣言すること、およびRETURNS SETOF
を指定することと全く等価です。
何らかのデータ型
PL/pgSQL関数の戻り値が多様型(38.2.5を参照)として宣言されると、特別な$0
パラメータが作成されます。
このデータ型が、実際の入力型から推定された関数の実際の戻り値の型です。
これにより、関数は43.3.3に示すように、実際の戻り値の型にアクセスできます。
$0
はNULLで初期化され、関数内で変更することができます。
ですので、必須ではありませんが、これを戻り値を保持するために使用しても構いません。
また$0
に別名を付与することもできます。
例えば、以下の関数は+
演算子を持つ任意のデータ型に対して稼働します。
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql;
1つ以上の出力パラメータを多様型として宣言することにより、同様の結果を得ることができます。
この場合、特殊な$0
パラメータは使用されません。
出力パラメータ自身が同じ目的を果たします。
以下に例を示します。
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement) AS $$ BEGIN sum := v1 + v2 + v3; END; $$ LANGUAGE plpgsql;
実際には型にanycompatible
族を使用して多様関数を宣言する方が有用である可能性があります。そうすれば、 入力引数が共通の型に自動的に昇格されます。
以下に例を示します。
CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible) RETURNS anycompatible AS $$ BEGIN RETURN v1 + v2 + v3; END; $$ LANGUAGE plpgsql;
この例は
SELECT add_three_values(1, 2, 4.7);
自動的に整数の入力を数値データに昇格して呼び出しが動作します。
anyelement
を使用する関数では、3つの入力を同じ型に手動でキャストする必要があります。
ALIAS
#newname
ALIAS FORoldname
;
ALIAS
構文は前節で示したものより一般的です。
関数の引数だけではなく、任意の変数に別名を宣言することができます。
この現実的な使用は主に、トリガ関数におけるNEW
やOLD
など、前もって決まった名前の変数に別の名前を割り当てることです。
以下に例を示します。
DECLARE prior ALIAS FOR old; updated ALIAS FOR new;
ALIAS
は同じオブジェクトを命名する2つの異なる手段を提供しますので、無制限に使用すると混乱を招くかもしれません。
前もって決まっている名前を上書きする目的に限定して使用することが最善です。
variable
%TYPE
%TYPE
は変数やテーブル列のデータ型を提供します。
これを使用してデータベース値を保持する変数を宣言することができます。
例えば、users
テーブルにuser_id
という列があるものとします。
users.user_id
と同じデータ型の変数を宣言するには、以下のように記述します。
user_id users.user_id%TYPE;
%TYPE
を使用することで、参照する構造のデータ型を把握する必要がなくなります。
また、これが最も重要なことですが、参照される項目のデータ型が将来変更された(例えば、user_id
のテーブル定義をinteger
からreal
に変更した)場合でも、関数定義を変更する必要をなくすことができます。
内部変数用のデータ型は呼び出す度に変わるかもしれませんので%TYPE
は特に多様関数で有用です。
関数の引数や結果用のプレースホルダに%TYPE
を適用することで、適切な変数を作成することができます。
name
table_name
%ROWTYPE
;name
composite_type_name
;
複合型の変数は、行変数(または行型変数)と呼ばれます。
こういった変数には、問い合わせの列集合が変数の型宣言と一致する限り、SELECT
やFOR
問い合わせの結果の行全体を保持することができます。
行変数の個々のフィールド値には、例えば、rowvar.field
といったドット記法を使用してアクセスすることができます。
table_name
%ROWTYPE
という記法を使用して、既存のテーブルやビューの行と同じ型を持つ行変数を宣言することができます。
もしくは、複合型の名前を付与して宣言することができます。
(全てのテーブルは、同じ名前の関連する複合型を持ちますので、実際のところPostgreSQLでは、%ROWTYPE
と書いても書かなくても問題にはなりません。
しかし、%ROWTYPE
の方がより移植性が高まります。)
関数へのパラメータとして複合型(テーブル行全体)を取ることができます。
その場合、対応する識別子$
は行変数であり、そのフィールドを、例えば、n
$1.user_id
で選択することができます。
以下に複合型を使用する例を示します。
table1
及びtable2
は、
少なくとも言及するフィールドを有する既存のテーブルです。
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$ DECLARE t2_row table2%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2 WHERE ... ; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
name
RECORD;
レコード変数は行型変数と似ていますが、事前に定義された構造を持っていません。
これはSELECT
やFOR
コマンドの間で代入された行の実際の行構造を取ります。
レコード変数の副構造は、代入を行う度に変更できます。
つまり、レコード変数は、最初に代入されるまで副構造を持たず、したがって、フィールドへのアクセスを試みると実行時エラーが発生します。
RECORD
は本当のデータ型ではなく、単なるプレースホルダであることに注意してください。
PL/pgSQL関数がrecord
型を返す時、この関数ではレコード変数を使用してその結果を保持することができますが、これはレコード変数としての概念とはまったく異なることを認識すべきです。
両方とも、関数の作成段階では実際の行構造は不明です。
しかし、レコード変数はその場その場でその行構造を変更できるにもかかわらず、record
を返す関数では呼び出し元の問い合わせが解析された時点で実際の構造は決定されます。
PL/pgSQL関数が照合可能なデータ型のパラメータを 1つ以上保有する場合、24.2に記述したように、実際の引数に割り当てられた照合に従って、関数呼び出し毎に照合が識別されます。 照合の識別に成功した場合(すなわち、引数の間に事実上の照合における衝突がない場合)、照合可能な全てのパラメータは暗黙の照合を有するとして扱われます。 これは関数内部において、照合に依存する操作の作用に影響します。 以下の例を考えてください。
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b; END; $$ LANGUAGE plpgsql; SELECT less_than(text_field_1, text_field_2) FROM table1; SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
第一の使用方法においてless_than
は、text_field_1
とtext_field_2
の比較のための通常の照合として用いられます。
第二の使用方法においては、C
照合として用いられます。
さらに、識別された照合は、照合可能なデータ型の全ての局所変数の照合としても仮定されます。 したがって、この関数は下に記述する関数と差異なく作動します。
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ DECLARE local_a text := a; local_b text := b; BEGIN RETURN local_a < local_b; END; $$ LANGUAGE plpgsql;
照合可能なデータ型のパラメータが存在しない場合、または、それらで共通する照合順序を識別できない場合、パラメータと局所変数は自身のデータ型のデフォルトの照合順序(通常これはデータベースのデフォルトの照合順序ですが、ドメイン型の変数の場合は異なるかもしれません)を使用します。
照合可能なデータ型の局所変数は、宣言内でCOLLATE
オプションを含めることにより、別の照合と関連づけることができます。
例を示します。
DECLARE local_a text COLLATE "en_US";
このオプションは上記ルールにより、変数に他の方法で付与されるはずであった照合を上書きします。
また当然ながら、強制的に特定の操作において特定の照合順序を使用したい場合、明示的なCOLLATE
句を関数内部に記述することができます。
例を示します。
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b COLLATE "C"; END; $$ LANGUAGE plpgsql;
単純なSQLコマンドで起こるように、これはテーブルの列、パラメータ、または式の中の局所変数に関連づけられた照合を上書きします