PostgreSQLのストアドプロシージャ

PostgreSQL		PostgreSQLのストアドプロシージャ
		2025/03/02

PL/pgSQLによるストアドファンクションとストアドプロシージャ

Oracle DatabaseがPL/SQLという独自の手続き型言語を内蔵しているように、 PostgreSQLにはPL/pgSQLという独自の言語が内蔵されており、この言語でコーディングすることで、ストアドファンクションとストアドプロシージャが定義できます。
ストアドパッケージはありませんが、スキーマをこまめに定義してその中でファンクションを定義すれば、まあまあパッケージのようなことができます。
ちなみに、Oracleのファンクションとプロシージャが戻り値を返すか返さないか程度の違いなのに対し、 PostgreSQLのファンクションとプロシージャは成り立ちが大きく異なることから「戻り値を返さないファンクション」と「プロシージャ」が別個のものとして定義できます。
その辺は色々あるので、少しずつ見ていきたいと思います。

ストアドファンクション

それではPL/pgSQLで記述したごく簡単なファンクションの例です。テーブル items から、単価(unit_price)の値が引数ani_price よりも大きいレコード数を返します。

CREATE OR REPLACE FUNCTION count_higher(ani_price int)
RETURNS int AS $$
DECLARE
  ln_count int := 0;
BEGIN

  SELECT COUNT(*) INTO ln_count
    FROM ITEMS
   WHERE UNIT_PRICE > ani_price;

  RETURN ln_count;
END;
$$ LANGUAGE plpgsql;

ほとんどまんまPL/SQLやないかーい！という所ですが、よく見ると何か所かに違いがあります。

戻り値のデータ型を表すキーワードはRETURNではなくRETURNSです。
処理本体はリテラルテキストである必要があるため「$$」で囲んでいます。
本体全体を囲む変数を宣言する場所でもDECLAREは省略できません。

ごく簡単な例での差異は以上ですが、そもそもSQL関数がOracleとPostgreSQLでは異なる上に、トランザクション制御など複雑な文になってくると色々違いがあります。それらはおいおい見ていくことにしたいと思います。

では、次にpsqlコマンドでこのファンクションを実行してみます。

mydb=> select count_higher(10000);
count_higher
------------
           3

ファンクションはSELECT文で実行できます。Oracleと異なり「FROM DUAL」を付ける必要はありません。

ストアドプロシージャ

次に戻り値を持たない代わりに、IN引数とOUT引数を持つストアドプロシージャを定義してみます。

CREATE OR REPLACE PROCEDURE MINMAX(
  IN avi_in1 varchar, IN avi_in2 varchar, OUT avo_min varchar, OUT avo_max varchar
) LANGUAGE plpgsql AS $$
BEGIN
  IF avi_in1 > avi_in2 THEN
    avo_min = avi_in2;
    avo_max = avi_in1;
  ELSE
    avo_min = avi_in1;
    avo_max = avi_in2;
  END IF;
END;
$$;

引数のINやOUTのキーワードの位置がOracleとは異なるので注意が必要です。
ちなみに関係ないですがIF文の文法はOracleと全く同じですね。

psqlで実行するときは次のようにCALL文を使います。

CALL MINMAX('March', 'April', NULL, NULL);

OUT引数に当たる個所はNULLを渡して引数の数を一致させる必要がある点に注意してください。もし

CALL MINMAX('March', 'April');

とすると、次のようなエラーになります。

ERROR:  procedure minmax(unknown, unknown) does not exist
LINE 1: CALL MINMAX('March', 'April');             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

独自の配列型を引数に持つストアドプロシージャ

CREATE DOMAIN MYARRAY AS VARCHAR[];

pgはOracleと違い、配列的な型をCREATE TYPEでは定義できません。代わりにDOMAINを使って上のように定義できます。
ではこれを引数に取るストアドプロシージャを定義してみます。

CREATE OR REPLACE PROCEDURE MINMAXM(
  IN ati_values MYARRAY, OUT avo_min varchar, OUT avo_max varchar
) LANGUAGE plpgsql AS $$
DECLARE
  s VARCHAR(100);
  lv_min_value VARCHAR(100) := 'ZZZZZZ';
  lv_max_value VARCHAR(100) := '';
BEGIN

  foreach s in ARRAY ati_values::VARCHAR[] loop
    IF s < lv_min_value THEN
      lv_min_value := s;
    END IF;
    IF s > lv_max_value THEN
      lv_max_value := s;
    END IF;
  end loop;
  avo_min := lv_min_value;
  avo_max := lv_max_value;
END;
$$;

foreachループで2点注意があります。まず「foreach 変数名 in 配列変数名」では駄目で、「foreach 変数名 in array 配列変数名」とする必要があること。 ::VARCHAR[]で配列にキャスト(強制型変換)を行う必要があることです。キャストを記述しないとコンパイルはできますが、実行時に「FOREACH expression must yield an array, not type myarray」というエラーになります。

		もくじ
PostgreSQL		(C) 2002-2025 MISUMI URANO (2002/01/12 - 2025/03/02)