stored procedure - hanaoka @ WIKI

stored procedure

PostgreSQL　プログラマガイド

　　　SQL の組み込み関数以外にユーザが作成する独自の関数.
　　　PL/pgSQL で作成する.
　　　標準インストール状態では、PL/pgSQL は使用できない.
　　　PL/pgSQL が使用可能かは、次のように調べる.

select * from pg_language;

lanname	lanispl	lanpltrusted	lanplcallfoid	lanvalidator	lanacl
internal	f	f	0	2246
c	f	f	0	2247
plpgsql	t	t	14852368	0
sql	f	t	0	2248	{=U/postgres}

　　　plpgsql がなければ、Cygwin のコマンドで、次のように入力し、plpgsqlが利用可能状態にする.

createlang -h HOSTNAME(もしくはIPアドレス) -d DBNAME -U USERNAME plpgsql

形

　　　典型的な例

DROP FUNCTION  sptest1();
CREATE FUNCTION sptest1() RETURNS INTEGER AS' 
DECLARE 
-- 変数の宣言 --
rec RECORD;
olduid VARCHAR;
newuid VARCHAR;
text_query TEXT;
-- メイン処理 --
BEGIN 
  -- ほかのプロシージャの呼び出し --
  -- [PERFORM]は戻り値が必要ない場合に指定 --
  PERFORM sptest2();
  -- 代入は「:=」 --
  text_query :=''select * from testtbl;''
RETURN 0;
END;'
LANGUAGE 'plpgsql';

実行方法
select sptest1();

ループ処理

　　　FOR　LOOPはカーソルを宣言していないが、暗黙のカーソルが内部的に使用されているらしい.

DROP FUNCTION sptest2();
CREATE FUNCTION sptest2() RETURNS INT AS'
DECLARE
  rec1 RECORD;
  rec2 RECORD;
BEGIN
FOR rec1 IN SELECT * FROM personalinfotbl WHERE uid =''*****'' AND gid = ''***'' 
LOOP
RAISE INFO ''gid = %  uid = %'', rec1.gid, rec1.uid;
  FOR rec2 IN SELECT * FROM personalinfotbl WHERE uid =''*****'' AND gid = ''***'' 
  LOOP
    RAISE INFO ''gid = %  uid = %'', rec2.gid, rec2.uid;
  END LOOP;
END LOOP;
return 0;
END;'
LANGUAGE 'plpgsql'
;

実行
SELECT sptest2();

エラー

relation with OID ****** does not exist

PL/PgSQL は関数スクリプトをキャッシュし,不幸にもその副作用で, PL/PgSQL関数が一時テーブルにアクセスする場合,後でそのテーブルを消し て作りなおされ,関数がもう一度呼び出されると,その関数はキャッシュし ている関数の内容はまだ古い一時テーブルを差し示したままだからです.この,解決策として、PL/PgSQLの中で EXECUTE を一時テー ブルへのアクセスのために使います.そうすると,クエリは毎回パースをや り直しされるようになります.

--sample--
text_query :=null;
text_query :='' SELECT * FROM tmpTbl '';
 for tmpTbl_rec in EXECUTE text_query 
 LOOP
  olduid := tmpTbl_rec.ouid;
  newuid := tmpTbl_rec.nuid;
  RAISE INFO ''OldUid: = %'',olduid;
  RAISE INFO ''NewUid: = %'',newuid;
 END LOOP

　　●Version7.4.6には「Exception」句はない(Ver8.0から)
　　　Exception句を使った例

CREATE OR REPLACE FUNCTION CATCH_ERR_TEST(INTEGER)
  RETURNS VOID AS '
DECLARE
  x ALIAS FOR $1;
  y integer;
BEGIN
  y := 1 / x;
  RETURN;
EXCEPTION
  WHEN division_by_zero THEN
     RAISE NOTICE ''0除算エラーを捕捉しました。'';
     RETURN;
END;
' LANGUAGE 'plpgsql';

実行例
% SELECT CATCH_ERR_TEST(0);
NOTICE: 0除算エラーを捕捉しました。

　　　Exception句を使わない例

DROP FUNCTION  test();
CREATE FUNCTION test() RETURNS VOID AS' 
DECLARE 
-- 変数の宣言 --
integer_var INTEGER;
nowdate TIMESTAMP;
BEGIN 
  insert into usertbl values(''Gxx'',''op-Gxx-tomoko2'',''tha0516'',2,1,''20070212'',''20070212'',''tomoko'');
  -- 影響を受けた行数を取得する
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''GET DIAGNOSTICS --> %'', integer_var;
  -- 影響を受けた行数を判断し、エラーを発生させる.
  -- するとこのトランザクションはアボートされる
  IF integer_var >0 THEN
    RAISE EXCEPTION ''Nonexistent ID --> %'', ''op-Gxx-tomoko2'';
  END IF;
  nowdate :=''now'';
  update usertbl set update_date=nowdate where gid=''Gxx'';
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''GET DIAGNOSTICS --> %'', integer_var;
RETURN; 
END;' 
LANGUAGE 'plpgsql'; 

実行例
SELECT test();
INFO:  GET DIAGNOSTICS --> 1
ERROR:  Nonexistent ID --> op-Gxx-tomoko2

　　　Exception句を使わない例２

DROP FUNCTION  test();
CREATE FUNCTION test() RETURNS VOID AS' 
DECLARE 
-- 変数の宣言 --
integer_var INTEGER;
BEGIN 
  insert into usertbl values(''Gxx'',''op-Gxx-tomoko5'',''tha0516'',2,1,''20070212'',''20070212'',''tomoko'');
  -- 影響を受けた行数を取得する
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''test() GET DIAGNOSTICS --> %'', integer_var;
  -- ここでtest1()を呼ぶ
  PERFORM test1();  
RETURN; 
END;' 
LANGUAGE 'plpgsql'; 

DROP FUNCTION  test1();
CREATE FUNCTION test1() RETURNS VOID AS' 
DECLARE 
-- 変数の宣言 --
integer_var INTEGER;
BEGIN 
  insert into usertbl values(''Gxx'',''op-Gxx-tomoko4'',''tha0516'',2,1,''20070212'',''20070212'',''tomoko'');
  -- 影響を受けた行数を取得する
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''test1() GET DIAGNOSTICS --> %'', integer_var;
  insert into usertbl values(''Gxx'',''op-Gxx-tomoko3'',''tha0516'',2,1,''20070212'',''20070212'',''tomoko'');
  -- 影響を受けた行数を取得する
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''test1() GET DIAGNOSTICS --> %'', integer_var;
  -- エラーを発生させる.
  IF integer_var >0 THEN
    RAISE EXCEPTION ''Nonexistent ID --> %'', ''op-Gxx-tomoko3'';
  END IF;
RETURN; 
END;' 
LANGUAGE 'plpgsql'; 

実行例
SELECT test();
実行結果を掲載すること！

　　test()の中でtest1()を呼び出す.
　　test1()の中ではエラーを発生させている.
　　すると,test()の中で発行されたINSERT文もrollbackされる.

transaction

●postgreSQLは,ネストしたトランザクションに対応していない.
※要するに,PostgreSQLのストアドプロシージャの内部では,トランザクションを開始したり,終了したりすることができない
bigin   :トランザクションブロックの開始
end     :現在のトランザクションのコミット.PostgreSQLの拡張で、COMMITと同一.
rollback:現在のトランザクションをロールバックし,そのトランザクションでおこなわれた全ての更新を廃棄.

結果ステータスの取得

　　●GET DIAGNOSTICS integer_var = ROW_COUNT;　を使う.
　　例

DROP FUNCTION  test();
CREATE FUNCTION test() RETURNS VOID AS' 
DECLARE 
-- 変数の宣言 --
integer_var INTEGER;
nowdate TIMESTAMP;
BEGIN 
  insert into usertbl values(''Gxx'',''op-Gxx-tomoko'',''tha0516'',2,1,''20070212'',''20070212'',''tomoko'');
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''GET DIAGNOSTICS --> %'', integer_var;
  nowdate :=''now'';
  update usertbl set update_date=nowdate where gid=''Gxx'';
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO ''GET DIAGNOSTICS --> %'', integer_var;
RETURN; 
END;' 
LANGUAGE 'plpgsql'; 

実行例
select test();
INFO:  GET DIAGNOSTICS --> 1
INFO:  GET DIAGNOSTICS --> 2