plpgsql関数の中でplpgsql関数を作成する

背景

今まであんまり真面目にplpgsqlを使ったことがなかったんだけど、今やっている仕事の一環として、デバッグ用のスクリプトをplpgsqlで組んでいて、ちょっと困ったことになっていた。

• 構造は同じなんだけど、テーブル名が異なるテーブル群に対して、1つの関数でテーブル名を指定して検索したい。
• こういう場合は関数内部でクエリ文字列を動的に生成して、EXECUTEで実行しないといけない。
• しかし、どうやら動的に生成したSQL（検索結果は複数行になる）の結果をうまく返せない。

解決案？

ということで、ちょっと考えてみたのだがplpgsqlの中では動的にSQLコマンドを生成して実行できるわけだから、CREATE FUNCTIONの中でCREATE FUNCTIONコマンドを記述して、動的に関数を生成し、その関数を実行出来るんじゃないかと思えてきた。
で、その動的に生成する関数内では静的なSQLとしてSELECT文を記述（実行時に決定するテーブル名なんかは文字列を生成するときに参照して生成し、生成したSQLの形としては静的なものにする）する。
生成した関数を実行し、その結果をQUERY RETURNで呼び元に返却してあげればいいんじゃないかと。

関数定義例

例えば、構造が同じ複数のテーブル(log_201204, log_201205, log_201206みたいなテーブル)があるとして、これに対して「テーブル名」「整数の検索条件値」を与えて検索するような関数を定義してみる。

CREATE OR REPLACE FUNCTION search_log(table_name text, value integer) RETURNS TABLE (data text) AS

$$

  -- 指定したtable_nameに対してvalueと等しい行を取得する。

DECLARE

  cf_sql text; -- CREATE FUNCTION用SQL

  df_sql text; -- DROP FUNCTION用SQL

BEGIN
  -- メモ：plpgsqlの制約（動的SQL実行による複数行返却不可）回避

  -- 一時的にCREATE FUNCTIONで関数を作成

  cf_sql := 'CREATE OR REPLACE FUNCTION tmp_search_log() RETURNS TABLE (data text) AS  $_$ SELECT data FROM ' || table_name || ' WHERE value = ' || value || ' $_$ LANGUAGE sql;';

  EXECUTE cf_sql;
  RETURN QUERY SELECT tmp_search_log() ;
  df_sql := 'DROP FUNCTION tmp_search_log()';

  EXECUTE df_sql;
  RETURN;

END;

$$ LANGUAGE plpgsql;

この関数(search_log)の中で、CREATE FUNCTION文のテキストをパラメータから生成し(cf_sql)、それをEXECUTEで実行して tmp_search_log という関数を登録する。
で、直後に RETURN QUERY SELECT tmp_search_log() で関数を実行し、その関数結果をRETURN QUERY でsearch_log関数の呼び元へ返却し、生成した tmp_search_log 関数は削除する。

実験

一応、動作を確認してみた。

INSERT INTO log_201204 VALUES( DEFAULT, generate_series(1,100) % 10, 'data' || ( (generate_series(1,100) % 10) )::text );

INSERT 0 100

INSERT INTO log_201205 VALUES( DEFAULT, generate_series(1,50) % 10, 'data' ||  ( (generate_series(1,10) % 10) )::text );

INSERT 0 50

INSERT INTO log_201206 VALUES( DEFAULT, generate_series(1,100) % 10, 'data' || ( (generate_series(1,100) % 10) )::text );

INSERT 0 100

SELECT search_log('log_201204', 5);

 search_log






















-










 data5

 data5

 data5

 data5

 data5

 data5

 data5

 data5

 data5

 data5

(10 rows)
SELECT search_log('log_201205', 4);

 search_log






















-










 data4

 data4

 data4

 data4

 data4

(5 rows)

うむ、一応うまく動いたみたいだ(valueと同じ値の行をのdataを返却している。dataはvalueの数値と同じ数字を"data"の後に付与した文字列を設定している)。