PostgreSQLのPL/pgSQLで複数行の戻り値を返す

SQL関連の記事が続いているので、今回もそれに乗っかってみました。

サーバーサイドの実装をしていると、少しややこしいが共通的な処理について、どこに実装するのが良いか迷う時があります。

基本的にはビジネスロジックに関する内容であれば、アプリケーションサーバーで実装し、共通的な処理だったらモジュール化してサーバーへデプロイして使います。 ただ、プログラムからだけでなくSQLからも使用する場合や、処理速度のためにアプリケーションサーバーとDB間の通信を減らしたい場合などは、ビジネスロジックに深く関わらない内容なら、DBにViewや関数として実装することもあります。

今回は、弊社で使用しているPostgreSQLのPL/pgSQL関数で複数行の結果を返す処理をを実装し、プログラムからは通常のテーブルをSELECTするように呼び出して使用した例を紹介します。

関数の作成

PL/pgSQLの関数は、以下の様に作成します。

CREATE OR REPLACE FUNCTION func_test(p_in_param varchar)
    RETURNS TABLE(
        id numeric(13,0),
        name varchar(128)
    ) AS $$
declare
    cur_food cursor(p_param varchar) for
        SELECT * FROM FOOD_TABLE WHERE FOOD_NAME ~ p_param
        ;
begin
    FOR rec_food IN cur_food(p_in_param) LOOP
        id := rec_food.food_id;
        name := rec_food.food_name;
        RETURN NEXT;
    END LOOP;
    RETURN;
end;
$$ LANGUAGE plpgsql;

ポイントは

• RETURNS TABLE句

戻り値として複数の項目(列)を返すために、RETRUNS TABLEとして、戻り値の構造体を定義します。

• RETURN NEXT句

RETURN TABLEで宣言した変数に値を入れてRETURN NEXTを呼ぶと、その列を戻り値として返すことができます。 この例では、カーソルでループする回数だけRETURN NEXTを呼ぶので、その件数の戻り値が返ります。

例では取得した値をそのまま戻り値の変数に入れていますが、ここに複雑な処理を実装することで、Viewでは複雑になりすぎるものも実装することができます。

クエリの実行

以下の様にSELECT句のFROMに関数を指定することで、クエリを実行できます。

database=> select * from func_test('ご飯');
   id   |                          name
--------+---------------------------------------------------------
 526583 | 人形町今半　牛炊き込みご飯　箱１６５ｇ
 526673 | はごろも　わかめご飯　鮭　袋２５０ｇ
 526674 | はごろも　わかめご飯　明太子昆布　袋２５０ｇ
 526675 | はごろも　わかめご飯　袋２５０ｇ
 537351 | カネカ　本格ごはん　鶏肉のバジル炒めご飯　袋８３ｇ
 543441 | マルちゃん　炊き込みご飯の素　鶏五目　袋４１．７ｇ
 543442 | マルちゃん　炊き込みご飯の素　鶏五目　袋１３．９ｇ×１０
 543443 | マルちゃん　炊き込みご飯の素　きのこ　袋１３．１ｇ×１０
(8 rows)

通常のクエリとして実行できるので、ORマッピングのフレームワーク等を使用して、簡単にオブジェクト化して使うことができると思います。

終わりに

個人的にはプログラムの実装はシンプルなのが好きなことと、アプリケーションサーバーとDB間のオーバーヘッドが気になるので、いわゆるビジネスロジックでは無いものをViewなどでDB側に置きたくなります。(処理が色々な所に分散されて管理が煩雑になることや、DBに負荷が集中してしまうデメリットも、もちろん気になりますが…。)

今回紹介した方法を使うと、クエリ時に少し複雑なロジックがあっても、ストアド関数として実装できて、選択の幅が広がると思いました。