ストアドプロシージャの移行（2）

実行方法の違い今回は前回の続きとして、ストアドプロシージャーの移行方法について説明します。

2005年12月9日 20:00

実行方法の違い

今回は前回の続きとして、ストアドプロシージャーの移行方法について説明します。

PL/SQLのストアドファンクションは、単一の値を返すため、SQL文の中で式として呼び出すことができます。逆にストアドプロシージャは、BEGIN〜ENDブロックの中で呼び出さなければいけません。

Oracleのストアドファンクションの実行方法

SELECT ファンクション名( 引数) FROM DUAL;

Oracleのストアドプロシージャの実行方法

BEGIN
   プロシージャ名( 引数);
END;

一方、PostgreSQLのストアドプロシージャは、常に単一の値を返すファンクションであるため、すべてSQL文の中で呼び出すことが可能です。

SELECT ファンクション名( 引数);

文字列

文字列は、少々やっかいです。Oracleではシングルクォーテーションを1つで文字列を囲むのに対して、PL/pgSQLでは2つ続けたもので囲まなければなりません。

Oracleでの文字列の囲み

CREATE FUNCTION HELLO() RETURN VARCHAR2 IS
 BEGIN
   RETURN 'HELLO, WORLD';
END;

PostgreSQLでの文字列の囲み

CREATE FUNCTION HELLO() RETURNS TEXT AS '
 BEGIN
   RETURN ''HELLO, WORLD'';
 END;
 ' LANGUAGE 'plpgsql';

これは前述したように、PostgreSQLではストアドプロシージャの本体部分を、いったん1つの文字列として構文解析するためです。このため、文字列の始まりと終わりに使うシングルクォーテーションを2回続けてエスケープする必要があるのです。文字列中にシングルクォーテーションを文字として出現させたい場合にも2回続きでエスケープしますので、この場合は4回続きになります。

CREATE FUNCTION MERRY() RETURNS TEXT AS '
BEGIN
  RETURN ''Merry, X''''mas!'';
END;
' LANGUAGE 'plpgsql';

これは、2回続きだと文字列の終わりとして認識されてしまうのを防ぐためです。

実行結果は次のようになります。

     merry
---------------
 Merry, X'mas!
(1 row)

特に、PL/pgSQLで動的SQLの機能を使った場合には、何重にもエスケープする場合が出てくるので、シングルクォーテーションが6回続きや10回続きなどになる場合も出てきます。いずれにせよ、PL/pgSQL中のシングルクォーテーションは必ず「偶数回になる」必要があるので、複雑なコードでエラーが出たら、シングルクォーテーションの数をよく調べてみるとよいでしょう。