Oracle PL/SQL から PostgreSQL PL/pgSQL への移植の注意メモ

マニュアルより引用


Oracle から PostgreSQL に移植するとき注意すべき点。
 総合:
* PostgreSQL にはデフォルトパラメータがありません。
* PostgreSQL では関数をオーバーロードすることができます。 これはしばしばデフォルトパラメータの欠如を補うために使われます。
* 代入とループと条件文は似ています。
* PostgreSQL ではカーソルは必要ありません。 FOR 文内に問い合わせを出力するだけです。 (以下の例をみてください)
* PostgreSQL では単一引用符をエスケープする必要があります。Section 19.11.1.1を参照してください。
  * PostgreSQL では文途中での commit/rollback が出来ない。関数全体でトランザクション制御を行う。
  * PostgreSQL ではEXECUTE 文を使用する場合に、 quote_literal(TEXT) と quote_string(TEXT)を使う事を忘れずに。
 関数:
* PostgreSQL には名前付きパラメータはありません。関数内部で明示的に別名を付ける必要があります。
  * PostgreSQL にはdefault パラメータが無い。
* Oracle は IN、OUT、 INOUT というパラメータを関数に渡すことができます。
    例えば、INOUT は、パラメータが値を受け取って別の値を返すことを意味します。
    PostgreSQL では “IN” パラメータしかないため、関数は単一の値しか返せません。
* 関数プロトタイプ内の RETURN キーワード(関数本体ではありません)は PostgreSQL では RETURNS になります。
* PostgreSQL 関数では単一引用符が区切り文字として使用されているので、関数内では単一引用符をエスケープしなければいけません(これが時々いらいらさせるんです。 Section 19.11.1.1を参照してください)。
* PostgreSQL には/show errors コマンドはありません。
 プロシージャ:
  * Oracle のプロシージャは、明示的に返り値を持つ必要が無いが、PostgreSQL では必要。
  * PostgreSQL には、pragma 文は無い。
* PL/pgSQL で LOCK TABLEを使用するならば、トランザクションの終了を呼び出すまでロックが解放されません。
  * PL/pgSQL プロシージャではトランザクションを使用することができません。
    関数全体(とそこから呼ばれる他の関数)は一つのトランザクションで実行されます。
そして、何か問題がおきたら、 PostgreSQL はその結果をロールバックします。そのため、BEGIN 文は一度だけ実行できます。
  * IF 文で置き換える必要のある例外
    PostgreSQL では、Exception 節が無い?
* PostgreSQL では、Package は無い。

Web サイトより引用(http://www.kjps.net/user/pipo6/pgfuncdef.html)


Oracleのストアドプロシージャ記述言語のPL/SQLに慣れている方が見たら、「何だこれわ!PL/SQLのパチモンじゃねーか!」と思われるかもしれませんが、それならば PL/pgSQL という名前を見た時点でピンと来なければ真の漢(をとこ)とは言えますまい!それはさておき、確かにPL/pgSQLの基本文法はPL/SQLに酷似しているので、慣れている方なら今日今すぐからでも書き始めることができるでしょう。
一応、見ればすぐに気づく主な違いを挙げると、次のようになります。
* PL/pgSQLでは CREATE OR REPLACE句は使えないので、 必ずDROP FUNCTION文とCREATE FUNCTION文を書かないといけません。
* PL/SQLでは引数はIN、OUT、IN OUTの3方向が指定できますが、PL/pgSQLでは、 引数はINしかとれません。
   また引数に名前は使わず型だけを指定します。 変数名はDECLARE節の中でALIAS FORを使って宣言します。
* 引数がない場合も、()と書かなくてはいけません。
* PL/SQLの RETURN はPL/pgSQLでは RETURNS です。
* PL/pgSQLでは処理ブロックをシングルクォートで囲むので、 ブロック内ではシングルクォートはエスケープして ” と書かなくてはいけません。
* PL/SQLでは最上位のDECLARE節の「DECLARE」は不要ですが、PL/pgSQL ではつけなくてはいけません。
PL/pgSQLによるユーザー定義関数の実際ですが、上のような例ですと、とりあえず「count_req_by_cdate.sql」いう名前ででもセーブしておいて、psqlから
udb=# i count_req_by_cdate.sql
CREATE
udb=#
このような感じで作成します。作ったユーザー定義関数を使うには、
udb=# select count_req_by_cdate(‘2001-09-02’);
上のようにSELECT文を使います。