本節ではOracle®からPostgreSQLへアプリケーションを移植する開発者の手助けとなるように、PostgreSQLのPL/pgSQL言語とOracleのPL/SQL言語の違いについて説明します。
PL/pgSQLは多くの点でPL/SQLに似ています。 それはブロックで構成されていて、厳格な言語であり、全ての変数は宣言されなければならない点です。 代入やループ、条件分岐も同様です。 PL/SQLからPL/pgSQLに移植する際に注意しなければならない、主な違いを以下に示します。
SQLコマンド内に使用された名前が、テーブルの列名または関数の変数への参照のどちらにもなり得る場合、PL/SQLは列名として処理します。
これはPL/pgSQLにおけるplpgsql.variable_conflict
= use_column
時の動作に対応しますが、42.11.1の説明通り、これはデフォルトではありません。
初期段階において、そのようなあいまいさを避けることが最善です。
しかしこの動作に依存するコードの量が多いものを移植しなければならない場合、variable_conflict
を使用することが最善の解法かもしれません。
PostgreSQLの関数本体は文字列リテラルとして書かなければなりません。 したがって、関数本体内部でドル引用符を使用するか、単一引用符をエスケープする必要があります。 (42.12.1を参照してください)。
データ型名はしばしば翻訳が必要です。
たとえば、Oracleでは文字列の値はよくvarchar2
型と宣言されますが、それは非標準SQL型です。
PostgreSQLでは、その代わりにvarchar
型またはtext
型を使ってください。
同様に、number
型はnumeric
型で置き換えるか、より適切なものがあるなら他の数値データ型を使ってください。
パッケージの代わりに、スキーマを使用して関数群をグループにまとめてください。
パッケージがないため、パッケージレベルの変数もありません。 これは幾分か厄介なことです。 代わって、セッションごとの状態を一時テーブル内部に保存できます。
REVERSE
を付けた整数FOR
ループの処理は異なります。
PL/SQLでは最後の数から最初の数へ減少しながら処理しますが、PL/pgSQLでは最初の数から最後の数へ減少しながら処理します。
移植において、ループの両端となる最初の数と最後の数を交換する必要があります。
この非互換性は不幸なことですが、変わりそうもありません。
(42.6.5.5を見てください。)
問い合わせ上のFOR
ループも(カーソルを除いて)異なって処理されます。
対象の変数は宣言されなければなりませんが、PL/SQLは常にそれらを暗黙的に宣言します。
この優位点は変数値をループを抜けてからでも依然としてアクセスできることです。
カーソル変数の使用に対する様々な表記上の違いがあります。
例 42.9に簡単な関数のPL/SQLからPL/pgSQLへの移植方法を示します。
例42.9 簡単な関数のPL/SQLからPL/pgSQLへの移植
以下はOracle PL/SQLの関数です。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2, v_version varchar2) RETURN varchar2 IS BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; / show errors;
この関数を通じて、PL/pgSQLとの違いを見てみましょう。
型名varchar2
は、varchar
またはtext
に変えなければなりません。
この節の例ではvarchar
を使いますが、文字列を特定の長さに制限する必要がないのであればtext
の方がたいていは良い選択です。
関数プロトタイプ内のRETURN
キーワード(関数本体ではありません)はPostgreSQLではRETURNS
になります。
同様にIS
はAS
になります。
PL/pgSQL以外の言語でも関数を記述できるため、LANGUAGE
句が必要となります。
PostgreSQLは関数本体を文字列リテラルと考えます。
したがって、それを囲むドル引用符または他の引用符が必要です。
これは/
で終了するOracleの方法の代替です。
PostgreSQLにはshow errors
コマンドはありません。
また、エラーが自動的に表示されるため、必要ありません。
それではPostgreSQLに移植されると、この関数がどのようになるか見てみましょう。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version varchar) RETURNS varchar AS $$ BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; $$ LANGUAGE plpgsql;
例 42.10は、他の関数を生成する関数を移植する方法、ならびに、その結果発生する引用符問題を扱う方法を示します。
例42.10 他の関数を生成するPL/SQLをPL/pgSQLに移植
以下の手続きは、SELECT
文からの行を取って、効率のためにIF
文で結果を巨大な関数に埋め込んでいます。
以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS CURSOR referrer_keys IS SELECT * FROM cs_referrer_keys ORDER BY try_order; func_cmd VARCHAR(4000); BEGIN func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2, v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN'; FOR referrer_key IN referrer_keys LOOP func_cmd := func_cmd || ' IF v_' || referrer_key.kind || ' LIKE ''' || referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type || '''; END IF;'; END LOOP; func_cmd := func_cmd || ' RETURN NULL; END;'; EXECUTE IMMEDIATE func_cmd; END; / show errors;
この関数をPostgreSQLで記述するとこうなるでしょう。
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$ DECLARE referrer_keys CURSOR IS SELECT * FROM cs_referrer_keys ORDER BY try_order; func_body text; func_cmd text; BEGIN func_body := 'BEGIN'; FOR referrer_key IN referrer_keys LOOP func_body := func_body || ' IF v_' || referrer_key.kind || ' LIKE ' || quote_literal(referrer_key.key_string) || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type) || '; END IF;' ; END LOOP; func_body := func_body || ' RETURN NULL; END;'; func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar, v_domain varchar, v_url varchar) RETURNS varchar AS ' || quote_literal(func_body) || ' LANGUAGE plpgsql;' ; EXECUTE func_cmd; END; $func$ LANGUAGE plpgsql;
関数本体を別途作成し、それをquote_literal
に渡して本体内の引用符を二重化する方法に注目してください。
新規の関数を定義する時ドル引用符の使用が安全とは限らないため、この方法が必要となります。
それはreferrer_key.key_string
の領域に、どのような文字列が書き込まれているか不明だからです。
(referrer_key.kind
は常に信用できるhost
かdomain
かurl
であると仮定しますが、どんなものでもreferrer_key.key_string
になり得るので、ドル記号を含む可能性があります。)
この関数はOracle版より実際に改善されています。
それはreferrer_key.key_string
またはreferrer_key.referrer_type
が引用符を含む時、おかしなコードを生成しないからです。
例 42.11は、OUT
パラメータを持ち、文字列操作を行う関数の移植方法を示します。
PostgreSQLには組み込みのinstr
関数はありませんが、他の関数を組み合わせることで作成できます。
42.13.3に、移植を簡略化できるようにinstr
のPL/pgSQLによる実装を示します。
例42.11 文字列操作を行い、OUT
パラメータを持つPL/SQLプロシージャのPL/pgSQLへの移植
以下のOracle PL/SQLプロシージャは、URLを解析していくつかの要素(ホスト、パス、問い合わせ)を返します。
以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR2, v_host OUT VARCHAR2, -- この値は戻されます v_path OUT VARCHAR2, -- この値も戻されます v_query OUT VARCHAR2) -- この値も戻されます IS a_pos1 INTEGER; a_pos2 INTEGER; BEGIN v_host := NULL; v_path := NULL; v_query := NULL; a_pos1 := instr(v_url, '//'); IF a_pos1 = 0 THEN RETURN; END IF; a_pos2 := instr(v_url, '/', a_pos1 + 2); IF a_pos2 = 0 THEN v_host := substr(v_url, a_pos1 + 2); v_path := '/'; RETURN; END IF; v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); a_pos1 := instr(v_url, '?', a_pos2 + 1); IF a_pos1 = 0 THEN v_path := substr(v_url, a_pos2); RETURN; END IF; v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); v_query := substr(v_url, a_pos1 + 1); END; / show errors;
これをPostgreSQLで記述すると、以下のようになります。
CREATE OR REPLACE FUNCTION cs_parse_url( v_url IN VARCHAR, v_host OUT VARCHAR, -- この値は戻されます v_path OUT VARCHAR, -- この値も戻されます v_query OUT VARCHAR) -- この値も戻されます AS $$ DECLARE a_pos1 INTEGER; a_pos2 INTEGER; BEGIN v_host := NULL; v_path := NULL; v_query := NULL; a_pos1 := instr(v_url, '//'); IF a_pos1 = 0 THEN RETURN; END IF; a_pos2 := instr(v_url, '/', a_pos1 + 2); IF a_pos2 = 0 THEN v_host := substr(v_url, a_pos1 + 2); v_path := '/'; RETURN; END IF; v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); a_pos1 := instr(v_url, '?', a_pos2 + 1); IF a_pos1 = 0 THEN v_path := substr(v_url, a_pos2); RETURN; END IF; v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); v_query := substr(v_url, a_pos1 + 1); END; $$ LANGUAGE plpgsql;
この関数は以下のように使用できます。
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
例 42.12は、Oracleに特化した多くの機能を使用したプロシージャの移植方法を示します。
例42.12 PL/SQLプロシージャのPL/pgSQLへの移植
以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS a_running_job_count INTEGER; BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE; SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL; IF a_running_job_count > 0 THEN COMMIT; -- ロックを解放 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.'); END IF; DELETE FROM cs_active_job; INSERT INTO cs_active_job(job_id) VALUES (v_job_id); BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); EXCEPTION WHEN dup_val_on_index THEN NULL; -- 既存であっても問題なし END; COMMIT; END; / show errors
それでは、このプロシージャをPL/pgSQLに移植することができた方法を見てみましょう。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$ DECLARE a_running_job_count integer; BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE; SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL; IF a_running_job_count > 0 THEN COMMIT; -- ロックを解放 RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1) END IF; DELETE FROM cs_active_job; INSERT INTO cs_active_job(job_id) VALUES (v_job_id); BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); EXCEPTION WHEN unique_violation THEN -- (2) -- 既存であっても問題なし END; COMMIT; END; $$ LANGUAGE plpgsql;
基本の | |
PL/pgSQLがサポートする例外の名称は、Oracleと異なります。 提供する例外の名称は、はるかに広範囲です(付録Aを参照してください)。 今のところ、ユーザ定義の例外名称を宣言できません。 しかし代わりにユーザが選択したSQLSTATE値を返すことができます。 |
本節では、Oracle PL/SQL関数のPostgreSQLへの移植における、その他の注意事項を説明します。
PL/pgSQLにおいてEXCEPTION
句が例外を捕捉すると、BEGIN
以降のそのブロックにおけるデータベースの変更が自動的にロールバックされます。
すなわち、OracleのPL/SQLによる以下のプログラムと同等の処理が実行されます。
BEGIN SAVEPOINT s1; ... code here ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... code here ... WHEN ... THEN ROLLBACK TO s1; ... code here ... END;
このような方式でSAVEPOINT
とROLLBACK TO
を使用したOracleのプロシージャの書き換えは簡単です。
単にSAVEPOINT
とROLLBACK TO
の処理を削除すればよいだけです。
これと異なった方式でSAVEPOINT
とROLLBACK TO
を使用したプロシージャの時は、それに応じた工夫が必要になると思われます。
EXECUTE
PL/pgSQLのEXECUTE
はPL/SQL版とよく似ています。
しかし42.5.4で説明されているquote_literal
とquote_ident
を使うことを覚えておかなければいけません。
これらの関数を使用しない限りEXECUTE ''SELECT * from $1'';
という構文の動作には、信頼性がありません。
PostgreSQLには実行を最適化するために2つの関数生成修飾子があります。 変動性(同じ引数が与えられた場合常に同じ結果を返します)と「厳密性」(引数のいずれかにNULLが含まれる場合NULLを返します)です。 詳細はCREATE FUNCTIONを参照してください。
これらの最適化属性を利用するためには、CREATE FUNCTION
文を以下のようにします。
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
本節には、移植作業を簡略化するために使用できる、Oracle互換のinstr
関数のコードがあります。
-- <!-- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1, string2 [, n [, m]]) -- where [] denotes optional parameters. --> -- Oracleのものと同じ動作をするinstr関数 -- 構文: instr(string1, string2 [, n [, m]]) ただし、[]は省略可能なパラメータ -- <!-- -- Search string1, beginning at the nth character, for the mth occurrence -- of string2. If n is negative, search backwards, starting at the abs(n)'th -- character from the end of string1. -- If n is not passed, assume 1 (search starts at first character). -- If m is not passed, assume 1 (find first occurrence). -- Returns starting index of string2 in string1, or 0 if string2 is not found. --> -- string1内のn番目の文字から始めて、m番目のstring2を探します。 -- nが負の場合、string1の終わりからabs(n)番目の文字から始めて、逆方向に検索します。 -- nが渡されなかった場合は、1とみなします(最初の文字から探し始めます)。 -- mが渡されなかった場合は、1とみなします(最初に一致するものを見つけます)。 -- string1内のstring2の開始位置を、string2が見つからなければ0を返します。 -- CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$ BEGIN RETURN instr($1, $2, 1); END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search_for varchar, beg_index integer) RETURNS integer AS $$ DECLARE pos integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; length integer; ss_length integer; BEGIN IF beg_index > 0 THEN temp_str := substring(string FROM beg_index); pos := position(string_to_search_for IN temp_str); IF pos = 0 THEN RETURN 0; ELSE RETURN pos + beg_index - 1; END IF; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search_for); length := char_length(string); beg := length + 1 + beg_index; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); IF string_to_search_for = temp_str THEN RETURN beg; END IF; beg := beg - 1; END LOOP; RETURN 0; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search_for varchar, beg_index integer, occur_index integer) RETURNS integer AS $$ DECLARE pos integer NOT NULL DEFAULT 0; occur_number integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; i integer; length integer; ss_length integer; BEGIN IF occur_index <= 0 THEN RAISE 'argument ''%'' is out of range', occur_index USING ERRCODE = '22003'; END IF; IF beg_index > 0 THEN beg := beg_index - 1; FOR i IN 1..occur_index LOOP temp_str := substring(string FROM beg + 1); pos := position(string_to_search_for IN temp_str); IF pos = 0 THEN RETURN 0; END IF; beg := beg + pos; END LOOP; RETURN beg; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search_for); length := char_length(string); beg := length + 1 + beg_index; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); IF string_to_search_for = temp_str THEN occur_number := occur_number + 1; IF occur_number = occur_index THEN RETURN beg; END IF; END IF; beg := beg - 1; END LOOP; RETURN 0; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;