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

[sugita ＠ sra.co.jp]

  杉田です。

From: 羽生　章洋 <habu ＠ air-beat.com>
Subject: [pgsql-jp: 26586] Re: PL/pgSQLで関数を作成したい
Date: Fri, 5 Jul 2002 15:30:05 +0900

;;; ■ストアドファンクション
;;; 
;;; 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';

  sql 関数はあんまりなので、常用している C のユーザ定義関数で連番を生成するも
のを使用した場合と実行時間を比較してみました。

    =# select gennum(10, 2);
     gennum 
    --------
	  2
	  3
	  4
	  5
	  6
	  7
	  8
	  9
	 10
	 11
    (10 rows)

  クエリーは以下のようにしています。

    SELECT * FROM (SELECT gennum(e, b)
	FROM (SELECT min(id) AS b, max(id) AS e FROM abc) AS s)
	    AS g
	    EXCEPT SELECT id FROM abc
	;


 データは、以下のようにしました。

    insert into abc values (1, 'aaa');
    insert into abc values (4, 'bbb');
    insert into abc values (5, 'ccc');
    insert into abc values (8, 'ddd');
    insert into abc values (10, 'eee');
    insert into abc values (10000, 'eee');
    か
    insert into abc values (100000, 'eee');
  
  計測結果は、次のようになりました。

    方式                        10000   100000
    ==========================  ======  ======
    ストアドファファンクション  0.901s  9.032s
    C で連番テーブル&EXCEPT     0.349s  3.440s	


Kenji Sugita