著者: Roberto Mello (<rmello@fslc.usu.edu>)
この節では開発者へオラクルから PostgreSQL へのアプリケーションの移 植の手助けとなるようオラクルの PL/SQL と PostgreSQL の PL/pgSQL の 違いについて説明します。ここにあるコードの大部分はArsDigita Clickstream moduleにあります。私は2000年の夏にOpenForce Inc.でインターンシッ プを行った時に PostgreSQL に移植しました。
PL/pgSQL は 多くの点で PL/SQL に似ています。それはブロックで構成さ れていて、厳格な言語であるということです。(全ての変数は宣言されなけ ればいけません。) PL/SQL はPostgrSQL に対応するものより多くの機能が あります、しかし PL/pgSQL には多くの機能があり、それは絶えず改善さ れています。
オラクルから PostgreSQL に移植するとき注意すべき点がいくつかあります。
PostgreSQL にはデフォルトパラメータがありません。
PostgreSQL では関数をオーバーロードすることができます。これはし ばしばデフォルトパラメータの欠如を補うために使われます。
割当てとループと条件文は似ています。
PostgreSQL ではカーソルは必要ないです、 FOR 文で問い合わせを出 力するだけです。(以下の例をみてください)
PostgreSQL ではシングルクォートをエスケープする必要 があります。Section 24.5.1.1を参照して ください。
PostgreSQL では、関数定義内ではシングルクォートをエスケープさせる 必要があります。他の関数を生成するような関数を作成する場合に特に 楽しいコードを書くことができます。Example 24-6 たくさんのシングルクォート をエスケープする際に注意をしておいていただきたいのは、最初と最後 のクォートは除かれることと、それ以外のすべては同じ数になることで す。
Table 24-1 はスクープです。(この小さ な図をきっと気に入ってくれることでしょう)
Table 24-1. シングルクォートのエスケープ図
番号 | 使い方 | 例 | 結果 |
---|---|---|---|
1 | 関数の中身の最初と最後 | CREATE FUNCTION foo() RETURNS INTEGER AS '...' LANGUAGE 'plpgsql'; | as is |
2 | 変数宣言や、SELECT 文中で文字列を囲む場合 | a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar''; | SELECT * FROM users WHERE f_name='foobar'; |
4 | 文字列が終了していないところで結果の文字列に二つのシングルクォートが必要な時 | a_output := a_output || '' AND name LIKE ''''foobar'''' AND ...'' | AND name LIKE 'foobar' AND ... |
6 | 結果として生じる文字列で二重引用符が欲しい場合 そしてその文字列を終了したい場合 | a_output := a_output || '' AND name LIKE ''''foobar'''''' | AND name LIKE 'foobar' |
10 | 結果の文字列に二つのシングルクォートが欲しい場合(8個の引用符 が必要) そしてはその文字列を終了させたい 場合(もう2 つ)関数を使って他の関数を生成させる場合にだけ必要 でしょう。(Example 24-6のよう に) | a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;''; | if v_<...> like ''<...>'' then return ''<...>''; end if; |
Example 24-5. 簡単な関数
これはオラクルの関数です。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN 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 との違いを見てみましょう。
OR REPLACE 句は使用できません。関数を作成す る前に明示的に関数を削除することで同じような結果を得ることがで きます。
PostgreSQLは引数に名前をつけられま せん。関数内で明示的にエイリアスしなければいけません。
オラクルにはIN, OUT, 、 INOUT 引数を関数に渡すことができます。例え ばINOUTは引数は一つの値をうけてそのほかを返 します。PostgreSQLでは"IN" 引数だけがあり、単一の 値だけ返すことができます。
関数プロトタイプ内の RETURN キーワード(関数 の中ではありません)は PostgreSQL では RETURNS になります。
PostgreSQL 関数ではシングルクォートがデリミタとして使用されて いるので、関数内ではシングルクォートをエスケープしなければいけ ません。(これが時々いらいらさせるんです。Section 24.5.1.1を参照してください)。
PostgreSQL には/show errorsコマンドはありません。
それではこの関数が PostgreSQL に移植されるとどのようになるかみてみ ましょう。
DROP FUNCTION cs_fmt_browser_version(varchar, varchar); CREATE FUNCTION cs_fmt_browser_version(varchar, varchar) RETRUNS varchar AS ' DECLARE v_name ALIAS FOR $1; v_version ALIAS FOR $2; BEGIN IF v_version IS NULL THEN return v_name; END IF; RETURN v_name || ''/'' || v_version; END; ' LANGUAGE 'plpgsql';
Example 24-6. 他の関数を作り出す関数
以下の手続きは SELECT 文からの行をとって、効率 のために IF 文で結果を巨大な関数にうめこんでい ます。PostgreSQL とはカーソル、FORループ、シン グルクォートをエスケープする必要があるというという違いに気づくで しょう。
create or replace procedure cs_update_referrer_type_proc is cursor referrer_keys is select * from cs_referrer_keys order by try_order; a_output varchar(4000); begin a_output := '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 a_output := a_output || ' if v_' || referrer_key.kind || ' like ''' || referrer_key.key_string || ''' then return ''' || referrer_key.referrer_type || '''; end if;'; end loop; a_output := a_output || ' return null; end;'; execute immediate a_output; end; / show errors
この機能を PostgreSQL で記述するとこうなるでしょう。
CREATE FUNCTION cs_update_referrer_type_proc() RETURNS integer AS ' DECLARE referrer_keys RECORD; -- Declare a generic record to be used in a FOR a_output varchar(4000); BEGIN a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) RETURNS varchar AS '''' DECLARE v_host ALIAS FOR $1; v_domain ALIAS FOR $2; v_url ALIAS FOR $3; ''; -- -- Notice how we scan through the results of a query in a FOR loop -- using the FOR <record> construct. -- FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;''; END LOOP; a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; -- This works because we are not substituting any variables -- Otherwise it would fail. Look at PERFORM for another way to run functions EXECUTE a_output; end; ' LANGUAGE 'plpgsql';
Example 24-7. たくさんの文字列操作手続きと、OUT 引数
以下のオラクルの PL/SQL 手続きは、URL をパースしていくつかの要素 (ホスト、パス、問い合わせ)を返します。PL/pgSQL 関数は一つの値のみ を返すので一つの手続きとなります(Section 24.5.3をご覧ください)。PostgreSQL ではこの関数は3つの異なる手続きに分割する方法があります。一つはホ ストを返し、もう一つはパスを返し、もう一つを問い合わせを返します。
create or replace procedure cs_parse_url( v_url IN varchar, v_host OUT varchar, -- This will be passed back v_path OUT varchar, -- This one too v_query OUT varchar) -- And this one is a_pos1 integer; a_pos2 integer; begin v_host := NULL; v_path := NULL; v_query := NULL; a_pos1 := instr(v_url, '//'); -- PostgreSQLには insrt 関数はありません 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 に移植したのがこのこの手続きです。
drop function cs_parse_url_host(varchar); create function cs_parse_url_host(varchar) returns varchar as ' declare v_url ALIAS FOR $1; v_host varchar; v_path varchar; a_pos1 integer; a_pos2 integer; a_pos3 integer; begin v_host := NULL; a_pos1 := instr(v_url,''//''); if a_pos1 = 0 then return ''''; -- Return a blank 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 v_host; end if; v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 ); return v_host; end; ' language 'plpgsql';
Note: PostgreSQL にはinstr 関数はないので、そのほ かの関数を組み合わせて使用することでその働きができます。私はこれ にあきてしまい、 オラクルのに似た(もっと簡単なものですが)独自の instr 関数を作成しました。コードはSection 24.5.6 をご覧下さい。
明示的に返り値をもつ必要がなくオラクルの手続きは開発者により柔軟性 を与えています。INOUT か OUT パラメータを使うことができます。
例
create or replace procedure cs_create_job(v_job_id in integer) is a_running_job_count integer; pragma autonomous_transaction; 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; -- free lock 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; -- don't worry if it already exists end; commit; end; / show errors
手続きは PostgreSQL 関数に INTEGER型を返すように簡単 に機能を変えることができます。これはいろいろな事を教えてくれること もあってこの手続きは特におもしろいです。
それで、この手続きを PL/pgSQL に移植することができた方法の一つを見 てみましょう。
drop function cs_create_job(integer); create function cs_create_job(integer) returns integer as ' declare v_job_id alias for $1; a_running_job_count integer; a_num integer; -- pragma autonomous_transaction; 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; -- free lock raise exception ''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); SELECT count(*) into a_num FROM cs_jobs WHERE job_id=v_job_id; IF NOT FOUND THEN -- If nothing was returned in the last query -- This job is not in the table so lets insert it. insert into cs_jobs(job_id, start_stamp) values(v_job_id, sysdate()); return 1; ELSE raise NOTICE ''Job already running.''; END IF; return 0; end; ' language 'plpgsql';
Note: 私はあまりパッケージを使ったことがないので、間違いがありましたら、 私に知らせてください。
パッケージはオラクルが、Javaクラスのように、1つの実体に PL/SQL 文 と関数をカプセル化するための方法です。ここではメソッドとオブジェク トの定義をします。これらはオブジェクト/メソッドに "."を使ってアクセスすることがでい ます。ここに ACS 4 のオラクルのパッケージの例があります。(ArsDigita Community System)
create or replace package body acs as function add_user ( user_id in users.user_id%TYPE default null, object_type in acs_objects.object_type%TYPE default 'user', creation_date in acs_objects.creation_date%TYPE default sysdate, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null, ... ) return users.user_id%TYPE is v_user_id users.user_id%TYPE; v_rel_id membership_rels.rel_id%TYPE; begin v_user_id := acs_user.new (user_id, object_type, creation_date, creation_user, creation_ip, email, ... return v_user_id; end; end acs; / show errors
標準的な命令規則を持つ関数としてオラクル・パッケージの異なるオブジェ クトを作成することでこれを PostgreSQL に移植します。PostgreSQL 関 数にはデフォルトパラメータが欠乏しているなどのいくつかの細かいこと に注意をはらわなければいけません、上記のパッケージはこのようなもの になるでしょう。
CREATE FUNCTION acs__add_user(integer,integer,varchar,datetime,integer,integer,...) RETURNS integer AS ' DECLARE user_id ALIAS FOR $1; object_type ALIAS FOR $2; creation_date ALIAS FOR $3; creation_user ALIAS FOR $4; creation_ip ALIAS FOR $5; ... v_user_id users.user_id%TYPE; v_rel_id membership_rels.rel_id%TYPE; BEGIN v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...); ... return v_user_id; END; ' LANGUAGE 'plpgsql';
PostgreSQL の EXECUTEは良くできています。しか しSection 24.2.5.3で説明 されているquote_literal(TEXT) と quote_string(TEXT) を使うことを覚えておかな ければいけません。これらの関数を使用しな限りEXECUTE ''SELECT * from $1'';のコンストラクタは働きません。
PostgreSQL は実行を最適化するために2つの機能生成モディファイアー があります。iscachable (同じ引数が与えられ常に 同じ結果が返されます) と isstrict (引数は NULL で NULL を返します). 詳細は CREATE FUNCTION をご覧ください。
これらの最適化属性を利用するためには、CREATE FUNCTION 文にWITH モディファイアーを 使用しなければいけません。
CREATE FUNCTION foo(...) RETURNS integer AS ' ... ' LANGUAGE 'plpgsql' WITH (isstrict, iscachable);
-- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params. -- -- Searches string1 beginning at the nth character for the mth -- occurrence of string2. If n is negative, search backwards. If m is -- not passed, assume 1 (search starts at first character). -- -- by Roberto Mello (rmello@fslc.usu.edu) -- modified by Robert Gaszewski (graszew@poland.com) -- Licensed under the GPL v2 or later. -- DROP FUNCTION instr(varchar,varchar); CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS ' DECLARE pos integer; BEGIN pos:= instr($1,$2,1); RETURN pos; END; ' language 'plpgsql'; DROP FUNCTION instr(varchar,varchar,integer); CREATE FUNCTION instr(varchar,varchar,integer) RETURNS integer AS ' DECLARE string ALIAS FOR $1; string_to_search ALIAS FOR $2; beg_index ALIAS FOR $3; 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'; -- -- Written by Robert Gaszewski (graszew@poland.com) -- Licensed under the GPL v2 or later. -- DROP FUNCTION instr(varchar,varchar,integer,integer); CREATE FUNCTION instr(varchar,varchar,integer,integer) RETURNS integer AS ' DECLARE string ALIAS FOR $1; string_to_search ALIAS FOR $2; beg_index ALIAS FOR $3; occur_index ALIAS FOR $4; 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';