ブロック内で使用される全ての変数はそのブロックの宣言部で宣言されなければなりません。(唯一の例外は、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 [ NOT NULL ] [ { DEFAULT | := } expression ];
DEFAULT句が指定された場合、ブロックに入った時に変数に代入される初期値を指定します。 DEFAULT句が指定されない場合、変数はSQLのNULL値に初期化されます。 CONSTANTオプションにより、そのブロック内でその値が不変になるように、その変数への代入は禁止されます。 NOT NULLが指定された場合、NULL値の代入は実行時エラーになります。 NOT NULLとして宣言した変数は全て、非NULLのデフォルト値を指定しなければなりません。
デフォルト値はブロックに入る度に評価されます。 ですから、例えば now() を timestamp 型の変数に代入することで、その変数には関数をプリコンパイルした時刻ではなく、関数呼び出し時の現在時刻が格納されます。
例
quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; user_id CONSTANT integer := 10;
関数に渡されるパラメータの名前には $1、$2 という識別子が付けられます。 オプションとして $n というパラメータ名に別名を宣言することができ、可読性が向上します。 別名、数字による識別子の両方とも引数の値を参照する時に使用することができます。 別名を作成する方法は2つあり、望ましい方法は CREATE FUNCTION コマンドの中でパラメータを命名するものです。 以下に例を示します。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
他の方法は PostgreSQL 8.0 より前でないと使用できませんが、宣言構文を用いて別名を明確に宣言するものです。
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;
さらに数例を示します。
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- 何らかの演算を行なう END; $$ LANGUAGE plpgsql; CREATE FUNCTION concat_selected_fields(in_t tablename) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql;
PL/pgSQL関数の戻り値が多様型(anyelementまたはanyarray)として宣言されると、特別な$0パラメータが作成されます。 このデータ型が、実際の入力型から推定(項31.2.5を参照)された関数の実際の戻り値型です。 $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;
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で選択することができます。
テーブル行のユーザ定義の属性のみに行型変数でアクセスすることができます。 OIDやその他のシステム属性にはアクセスできません(ビューからの行があり得るためです)。 行型のフィールドは、例えばchar(n)などのテーブルのフィールドの大きさやデータ型の精度を継承します。
以下に複合型を使用する例を示します。
CREATE FUNCTION merge_fields(t_row tablename) RETURNS text AS $$ DECLARE t2_row table2name%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2name WHERE ... ; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; SELECT merge_fields(t.*) FROM tablename t WHERE ... ;
name RECORD;
レコード変数は行型変数と似ていますが、事前に定義された構造を持っていません。 これはSELECTやFORコマンドの間で代入された行の実際の行構造をとります。 レコード変数の副構造は、代入を行う度に変更できます。 つまり、レコード変数は、最初に代入されるまで副構造を持たず、従って、フィールドへのアクセスを試みると実行時エラーが発生します。
RECORDは本当のデータ型ではなく、単なるプレースホルダであることに注意してください。 PL/pgSQL関数がrecord型を返す時、この関数はその結果を保持するレコード変数を問題なく使用できますが、これはレコード変数としての概念とは全く異なることを認識すべきです。 両方の場合、関数の作成段階では実際の行構造は不明です。 しかし、recordを返す関数では、レコード変数はその場その場でその行構造を変更できるにも関わらず、呼出し元の問い合わせが解析された時点で実際の構造は決定されます。
RENAME oldname TO newname;
RENAME宣言を使用して、変数、レコード、行の名前を変更することができます。 これは主に、トリガプロシージャの内側でNEWやOLDを別の名前で参照しなければならない場合に有用です。 ALIASも参照してください。
以下に例を示します。
RENAME id TO user_id; RENAME this_var TO that_var;
注意: RENAMEは、PostgreSQL 7.3の時点では壊れているようです。 ALIASでRENAMEの実用的な使用方法のほとんどを代用できますので、この修正は優先順位は低いです。