本節ではOracle®からPostgreSQLへアプリケーションを移植する開発者の手助けとなるように、PostgreSQLのPL/pgSQL言語とOracleのPL/SQL言語の違いについて説明します。
PL/pgSQLは多くの点でPL/SQLに似ています。 それはブロックで構成されていて、厳格な言語であり、全ての変数は宣言されなければならない点です。 代入やループ、条件分岐も同様です。 PL/SQLからPL/pgSQLに移植する際に注意しなければならない、主な違いを以下に示します。
SQL コマンド内に使用された名前が、テーブルの列名または関数内の変数への参照のどちらにもなりうる場合、PL/SQLは列名として処理しますが、PL/pgSQLは変数名として処理します。 初期段階において、そのような曖昧さを避けることが最善です。 しかし、必要ならば通常のドットを付けた修飾により、曖昧な名前を固定できます(項38.10.1を見てください)。
PostgreSQL の関数本体は文字列リテラルとして書かなければなりません。 したがって、関数本体内部でドル引用符を使用するか、単一引用符をエスケープする必要があります。 (項38.11.1を参照してください)。
パッケージの代わりに、スキーマを使用して関数群をグループにまとめてください。
パッケージがないため、パッケージレベルの変数もありません。 これは幾分か厄介なことです。 代わって、セッションごとの状態を一時テーブル内部に保存できます。
REVERSEを付けた整数FORループの処理は異なります。 PL/SQLでは最後の数から最初の数へ減少しながら処理しますが、PL/pgSQLでは最初の数から最後の数へ減少しながら処理します。 移植において、ループの両端となる最初の数と最後の数を交換する必要があります。 この非互換性は不幸なことですが、変わりそうもありません。 (項38.6.3.5を見てください)。
問い合わせ上のFORループは(カーソルを除いて)同様異なって処理されます。ターゲット変数は宣言されなければなりませんが、PL/SQLは常にそれらを暗黙的に宣言します。この優位点は変数値をループを抜けてからでも依然としてアクセスできることです。
カーソル変数の使用に対する多様な表記上の違いがあります。
例38-6に簡単な関数のPL/SQLからPL/pgSQLへの移植方法を示します。
例 38-6. 簡単な関数のPL/SQLからPL/pgSQLへの移植
以下はOracle PL/SQLの関数です。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version varchar) RETURN varchar IS BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; / show errors;
この関数を通じて、PL/pgSQLとの違いを見てみましょう。
関数プロトタイプ内の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;
例38-7は、他の関数を生成する関数を移植する方法、ならびに、その結果発生する引用符問題を扱う方法を示します。
例 38-7. 他の関数を生成する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 VARCHAR, v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR 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 FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$ DECLARE CURSOR referrer_keys 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が引用符を含む時、おかしなコードを生成しないからです。
例38-8は、OUTパラメータを持ち、文字列操作を行う関数の移植方法を示します。
PostgreSQLには組み込みのinstr
関数はありませんが、他の関数を組み合わせることで作成できます。
項38.12.3に、移植を簡略化できるようにinstr
のPL/pgSQLによる実装を示します。
例 38-8. 文字列操作を行い、OUTパラメータを持つPL/SQLプロシージャのPL/pgSQLへの移植
以下のOracle PL/SQLプロシージャは、URLを解析していくつかの要素(ホスト、パス、問い合わせ)を返します。
以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR, v_host OUT VARCHAR, -- この値は戻されます v_path OUT VARCHAR, -- この値も戻されます v_query OUT VARCHAR) -- この値も戻されます 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');
例38-9は、Oracleに特化した多くの機能を使用したプロシージャの移植方法を示します。
例 38-9. PL/SQLプロシージャのPL/pgSQLへの移植
以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS a_running_job_count INTEGER; PRAGMA AUTONOMOUS_TRANSACTION;(1) BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE;(2) SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL; IF a_running_job_count > 0 THEN COMMIT; -- ロックを解放(3) 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, sysdate); EXCEPTION WHEN dup_val_on_index THEN NULL; -- 既存であっても問題なし END; COMMIT; END; / show errors
このようなプロシージャはvoid型を返すPostgreSQL関数に簡単に変換することができます。 以下のようなことを教えてくれることもあって、このプロシージャは特におもしろいものです。
それでは、このプロシージャをPL/pgSQLに移植することができた方法を見てみましょう。
CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void 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 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; END; $$ LANGUAGE plpgsql;
このプロシージャはOracle版に比べて機能的な違いがあります。 それはcs_jobsテーブルの排他的ロックがトランザクションの終了まで継続することです。 同様に、関数呼び出しの後で(例えばエラーが原因で)アボートすると、プロシージャの影響はロールバックされます。
本節では、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を使用したプロシージャの時は、それに応じた工夫が必要になると思われます。
PL/pgSQLのEXECUTEはPL/SQL版とよく似ています。
しかし項38.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
関数のコードがあります。
-- -- Oracleのものと同じ動作をするinstr関数 -- 構文: instr(string1,string2,[n],[m]) ただし、[]は省略可能なパラメータ -- -- string1内のn番目の文字からm番目の文字まででstring2を探します。 -- nが負の場合、逆方向に検索します。 mが渡されなかった場合は、 -- 1とみなします(最初の文字から検索を始めます)。 -- CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$ DECLARE pos integer; BEGIN pos:= instr($1, $2, 1); RETURN pos; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search 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 IN temp_str); IF pos = 0 THEN RETURN 0; ELSE RETURN pos + beg_index - 1; END IF; ELSE ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 THEN RETURN beg; END IF; beg := beg - 1; END LOOP; RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search 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 beg_index > 0 THEN beg := beg_index; temp_str := substring(string FROM beg_index); FOR i IN 1..occur_index LOOP pos := position(string_to_search IN temp_str); IF i = 1 THEN beg := beg + pos - 1; ELSE beg := beg + pos; END IF; temp_str := substring(string FROM beg + 1); END LOOP; IF pos = 0 THEN RETURN 0; ELSE RETURN beg; END IF; ELSE ss_length := char_length(string_to_search); length := char_length(string); beg := length + beg_index - ss_length + 2; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str); IF pos > 0 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; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;