PostgreSQL めも - ストアドプロシージャ

※正確には、PostgreSQLのユーザ定義関数ことについて、めも。
※PL/pgSQL利用前提
※個人用(ウソがあったらゴメン)
※そして書くだけ書いて検証してないもの多数かも
※避難場所はこちらｗ http://www.postgresql.jp/document/9.0/html/plpgsql.html
※こちら、大いに参考になりました。ขอบคุณมากๆครับ
【plpgsql】プロシージャ内SQL、引数展開位置に注意

・関数の定義
マニュアルサイトやサンプルで「$$」が使われてることが多かったから気にせず鵜呑みしてたが、「$hanuman$」でも、「'」でも、なんでもよい。それで始まり、それで終わるブロックが関数の処理部ということになる。
※ほんとに「なんでも」よいか、は未調査。
※「'」を用いた場合は、処理部分で「'」を使う場合に当然エスケープが必要になる「''」

・変数への代入 :=

-- sample: 999を食え

araiwa := 999;

・文字列結合 ||

-- sample: ใครขายไข่ไก่

araiwa := 'ใคร' || 'ขาย' || 'ไข่' || 'ไก่';
-- ใครขายไข่ไก่

・クエリ発行
いつもやってるようにクエリをそのまんま記述してやれば実行されるが、SELECT結果を利用して後続の処理をする場合などは、やりたい内容に応じて記述は異なる。

-- sample: SELECT結果行数分回れ！1

CREATE FUNCTION func_hanuman_1() RETURNS INTEGER AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN SELECT id FROM t_hanuman LOOP
    RAISE NOTICE 'id = %', record.id;
  END LOOP;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

・EXECUTE文
が、上のサンプルで、SELECT文内に変数を使いたい場合、EXECUTE文が必要となる。これ、ハマった。。。マニュアルにも「39.5.4. 動的コマンドの実行」で書いてあるけど、これがそれのことだと気付くまでえらい時間がかかった。。。リテラシー低すぎかなぁ。

-- sample: SELECT結果行数分回れ！2

CREATE FUNCTION func_hanuman_2() RETURNS INTEGER AS $$
DECLARE
  row   RECORD;
  sql   TEXT;
  name  TEXT := 'shin';
BEGIN
  sql := 'SELECT id, name FROM t_hanuman WHERE name ~ ''%'
           || name || '%'' ORDER BY id';
  FOR row IN EXECUTE sql LOOP
    RAISE NOTICE 'name = %', record.name;
  END LOOP;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

・RETURNS TABLE
関数の戻り値として、SELECT結果(複数レコード)を返したいとき RETURNS TABLE でやる。TABLE() の引数はSELECTで取得するカラムに合わせる。カラム名と同じ引数名にしたらアカン！

-- sample: SELECT結果行を返せ！

CREATE FUNCTION func_hanuman_3()
RETURNS TABLE(col1 int, col2 text) AS $$
DECLARE
  sql TEXT;
BEGIN
  sql := 'SELECT id, name FROM t_hanuman ORDER BY id';
  RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE plpgsql;