PL/pgSQL

PL/pgSQLはPostgreSQLで使える手続き型言語である。OracleにおけるPL/SQLに当たる。PL/pgSQLはPL/SQLを参考に実装したらしい。
PL/pgSQLの構造は下記のようなブロック構造になっている。

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

コメントは2種類ある
・"--" からその行の終わりまで。
・"/*" で始まり "*/" で終わる。

例1：2012/01/01～2013/01/31までの日付をINSERTする。値は何も返さない。

CREATE OR REPLACE FUNCTION test_func_01()
RETURNS void AS '
DECLARE
  tmp date;
BEGIN
  tmp := ''2012-01-01''::date;
  FOR i IN 1..731 LOOP
    INSERT INTO t_result (id, type, date) VALUES (i, ''A'', tmp);
    tmp := tmp + interval ''1 day'';
  END LOOP;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT test_func_01();
 test_func_01
--------------

(1 行)

dbtest=> SELECT * FROM t_result ORDER BY id;
 id  | type |        date
-----+------+---------------------
   1 | A    | 2012-01-01 00:00:00
   2 | A    | 2012-01-02 00:00:00
…
 730 | A    | 2013-12-30 00:00:00
 731 | A    | 2013-12-31 00:00:00
(731 行)

dbtest=>

例2：SELECTを3回実行し、その結果をまとめて返す。

CREATE OR REPLACE FUNCTION test_func_02(OUT a integer, OUT b char(1), OUT c timestamp)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..3 LOOP
    SELECT * from t_result WHERE id = i INTO a, b, c;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT * FROM test_func_02();
 a | b |          c
---+---+---------------------
 1 | A | 2012-01-01 00:00:00
 2 | A | 2012-01-02 00:00:00
 3 | A | 2012-01-03 00:00:00
(3 行)

dbtest=>

例3：カーソルを使う。

CREATE OR REPLACE FUNCTION test_func_03(OUT a timestamp, OUT dummy integer)
RETURNS SETOF RECORD AS '
DECLARE
  cur CURSOR FOR SELECT * FROM t_result ORDER BY id;
  r t_result;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO r;
    SELECT r.date INTO a;
    RETURN NEXT;
    IF r.id = 3 THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

実行結果

dbtest=> SELECT * FROM test_func_03();
          a          | dummy
---------------------+-------
 2012-01-01 00:00:00 |
 2012-01-02 00:00:00 |
 2012-01-03 00:00:00 |
(3 行)

dbtest=>

複数のクエリーの結果を返す場合で、よくわからないな現象があった。
2列を返す下記の関数は特に問題なく定義できて実行もできた。

CREATE OR REPLACE FUNCTION test1(OUT a integer, OUT b integer)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..10 LOOP
    SELECT i, i+1 INTO a, b;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

しかし、返す値を1列にすると定義自体がエラーになる。

CREATE OR REPLACE FUNCTION test2(OUT a integer)
RETURNS SETOF RECORD AS '
BEGIN
  FOR i IN 1..10 LOOP
    SELECT i INTO a;
    RETURN NEXT;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

エラーメッセージは以下。

ERROR:  OUTパラメータのため、関数の戻り値型はintegerでなければなりません。

"SELECT i" の i に "::integer" と付けても同じエラー。意味がわからない。
例3のOUTパラメータdummyはこのエラーを避けるために付けた。

参考サイト：
PostgreSQL 8.4.4文書 第38章 PL/pgSQL - SQL手続き言語
複数行複数列を返すPL/pgSQL関数 - iakioの日記 - postgresqlグループ
PostgreSQL初心者の悪戦苦闘ぶりをメモする pl/pgsql篇 | 読書と技術となんか色々のログ - 楽天ブログ