[pgsql-jp: 26586] Re: PL/pgSQLで関数を作成したい

[羽生 章洋]

Kazusi Nakamura <kazz ＠ emsg.jp>さん：

> ありがとうございました。バージョンアップを検討してみます。

UPされていたコードを元に作ってみました。適当に修正して
使ってください。

尚、一時テーブルを使用しているのですが、一度セッションが切れると
再度作成しなおす必要があるので、実行時にはご注意ください。

一時テーブルの作成は、
create temporary table tmp(empty_id integer);
とします。

＃　この辺は適当にもっと上手い方法を探してください。

■ストアドファンクション

create or replace function GetEmptyID(int, int)
 returns REFCURSOR as '
    declare
        i_fromID alias for $1;
        i_toID alias for $2;
        cnttbl integer;
        EmptyID integer := 0;
        m_ID record;
        ret REFCURSOR;
    begin

        delete from tmp;

        FOR m_ID IN SELECT id FROM a
        WHERE id BETWEEN i_fromID AND i_toID LOOP
            IF(EmptyID = m_ID.id) THEN
                EmptyID := EmptyID + 1;
            ELSE
                WHILE (EmptyID < m_ID.id) LOOP
                    INSERT INTO tmp VALUES (EmptyID);
                    EmptyID := EmptyID + 1;
                END LOOP;
                EmptyID := EmptyID + 1;
            END IF;
        END LOOP;

        OPEN ret FOR select * from tmp;
        RETURN ret;
     end;
' LANGUAGE 'plpgsql';

■実行のしかた（PSQLより）

$ psql iruka

iruka=# create temporary table tmp(empty_id integer);
CREATE
iruka=# begin;
BEGIN
iruka=# select GetEmptyID(1,10);
     getemptyid
--------------------
 <unnamed cursor 6>
(1 row)

iruka=# fetch all in "<unnamed cursor 6>";
 empty_id
----------
        0
        2
        3
        6
        7
        9
(6 rows)

iruka=# select GetEmptyID(1,10);
     getemptyid
--------------------
 <unnamed cursor 8>
(1 row)

iruka=# fetch all in "<unnamed cursor 8>";
 empty_id
----------
        0
        2
        3
        6
        7
        9
(6 rows)

iruka=# end;
COMMIT
iruka=# \q


■注意点

最初にゼロが入ってしまいますが、この辺はカーソルを開くときの
SELECT文にWHEREをつけるなどして回避してください。

カーソル名は明示せずに毎回取得したものを使うようにしています。
呼び出すホスト言語側でハンドリングしてください。


■おまけ：呼び出すJavaのソースコード例

import java.sql.*;

public class test {
  public static void main(String[] args) {
      try {
          Class.forName("org.postgresql.Driver");
      } catch (Exception ex) {}

      try {
        Connection conn =
              DriverManager.getConnection(
              "jdbc:postgresql://localhost/iruka",
              "iruka",
              ""
              );

      Statement stmt = conn.createStatement();
      ResultSet rs;

      String sql = "begin; select GetEmptyID(1,10)";
      rs = stmt.executeQuery(sql);
      rs.next();
      String cursorName = rs.getString(1);
      sql = "fetch all in \"" + cursorName + "\"; ";
      rs = stmt.executeQuery(sql);

      int c1;
      String c2;

      while(rs.next()) {
        c1 = rs.getInt("c1");
        c2 = rs.getString("c2");
        System.out.println(c1 + " : " + c2);
      }
      sql = "end;";
      stmt.execute(sql);
      rs.close();
      stmt.close();
      conn.close();
    }
    catch(SQLException ex){
      ex.printStackTrace();
    }
    catch(Exception ex){
      ex.printStackTrace();
    }

  }
}

ストアドファンクションを呼び出すときの引数は直値にしてしまってるので、
変数に変えてください。

・・・以上、参考になれば幸いです。


================================================================
★ワクワク音楽体験サイト　それが【エア・ビート・ドットコム】！★
================================================================
有限会社エア・ビート(http://www.air-beat.com/)
取締役兼CEO　羽生　章洋(mailto:habu ＠ air-beat.com)
================================================================