はじめに
PL/pgSQLに関して、個人的に作成したサンプルコードをまとめた記事となります。
基本的にPL/pgSQLの仕様については公式ドキュメントを見れば理解できると思いますが、
サンプルコードが少なく、どう実装すればいいんだっけ？となる場面も多々あったので、
この記事がお役に立てば幸いです。
PostgreSQLのバージョンについて
本記事におけるpostgreSQLのバージョンは12.4で扱っていきます。
目次
事前準備(テストデータ投入)
今回のサンプルコードを作成するにあたり、検証用の環境を用意しました。
検証する際に是非活用してください。
なお、テストデータ投入そのものにPL/pgSQLを利用しています。
テストデータ作成SQL
test.sql
/*
* スキーマ作成
*/
create schema test_plpgsql;
/*
* DDL作成
*/
create table test_plpgsql.dept (
deptno char(5) primary key,
deptname varchar(40) unique not null
);
create table test_plpgsql.pos (
poscode char(1) primary key,
posname varchar(20) unique not null
);
create table test_plpgsql.emp (
empno char(5) primary key,
empname varchar(40) not null,
poscode char(1) not null references test_plpgsql.pos(poscode),
age numeric(3,0) check(age >=0)
);
create table test_plpgsql.member (
deptno char(5) not null references test_plpgsql.dept(deptno),
empno char(5) not null references test_plpgsql.emp(empno),
primary key(deptno, empno)
);
create table test_plpgsql.test (
col1 INTEGER primary key,
col2 TEXT
);
-- 複合型のユーザ定義
create type test_plpgsql.DATA_TYPE1 as (
param1 numeric(1,0)
,param2 text
,param3 bytea
);
-- 複合型のユーザ定義型(入れ子パターン)
create type test_plpgsql.DATA_TYPE2 as (
param1 numeric(1,0)
,param2 test_plpgsql.DATA_TYPE1
);
create sequence test_plpgsql.empno_seq;
/*
* DML作成
*/
CREATE OR REPLACE PROCEDURE test_plpgsql.main() AS $$
DECLARE
BEGIN
SET SEARCH_PATH='test_plpgsql';
perform setval ('empno_seq', 1, false);
-- マスタデータ登録
-- 部署
INSERT INTO test_plpgsql.dept VALUES ('0','営業部');
INSERT INTO test_plpgsql.dept VALUES ('1','開発部');
INSERT INTO test_plpgsql.dept VALUES ('2','人事部');
-- 役職
INSERT INTO test_plpgsql.pos VALUES ('0', '部長');
INSERT INTO test_plpgsql.pos VALUES ('1', '課長');
INSERT INTO test_plpgsql.pos VALUES ('2', '主任');
INSERT INTO test_plpgsql.pos VALUES ('3', '社員');
--社員
CALL test_plpgsql.regemployee(1,'0','0','営業部長',45);
CALL test_plpgsql.regemployee(1,'1','0','開発部長',45);
CALL test_plpgsql.regemployee(1,'2','0','人事部長',45);
CALL test_plpgsql.regemployee(3,'0','1','営業課長',40);
CALL test_plpgsql.regemployee(3,'1','1','開発課長',40);
CALL test_plpgsql.regemployee(3,'2','1','人事課長',40);
CALL test_plpgsql.regemployee(30,'0','2','営業主任',35);
CALL test_plpgsql.regemployee(30,'1','2','開発主任',35);
CALL test_plpgsql.regemployee(30,'2','2','人事主任',35);
CALL test_plpgsql.regemployee(300,'0','3','営業社員',30);
CALL test_plpgsql.regemployee(300,'1','3','開発社員',30);
CALL test_plpgsql.regemployee(300,'2','3','人事社員',30);
END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE test_plpgsql.regemployee(count IN int, deptno IN char, poscode IN char, emponame IN varchar, age IN int)
AS $$
BEGIN
IF count < 1 THEN
RETURN;
ELSEIF count = 1 THEN
INSERT INTO test_plpgsql.emp VALUES ((SELECT nextval('empno_seq')), emponame, poscode ,age);
INSERT INTO test_plpgsql.member VALUES (deptno, (SELECT currval('empno_seq')));
ELSE
FOR i IN 1..count LOOP
INSERT INTO test_plpgsql.emp VALUES ((SELECT nextval('empno_seq')), emponame || i, poscode ,age);
INSERT INTO test_plpgsql.member VALUES (deptno, (SELECT currval('empno_seq')));
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION '例外発生SQLSTATE:%, SQLERRM:%', SQLSTATE,SQLERRM;
END;
$$
LANGUAGE plpgsql;
CALL test_plpgsql.main();
PL/pgSQLの構造
ファンクションについて
まずは「HELLO WORLD」ができるサンプルコードを作成してみましょう。
サンプルコード内のRAISE INFOを用いて「HELLO WORLD！！」というメッセージをコンソールに出力させます。
サンプルコード
CREATE OR REPLACE FUNCTION test_plpgsql.sample1_01() RETURNS VOID AS $$
DECLARE
BEGIN
RAISE INFO 'HELLO WORLD！！';
RETURN;
END;
$$ LANGUAGE plpgsql;
ファンクションの作成
CREATE OR REPLACE FUNCTION ファンクション名(引数) RETURNS 戻り値
で作成することができます。
CREATEのみでも作成できますが、CREATE OR REPLACE としておくことで、
同一の引数、戻り値が存在する関数が存在する場合に処理内容を上書きすることが可能です。
ファンクションの実行
SELECT ファンクション名(引数);で実行することが可能です
実行結果
example-db=> SELECT test_plpgsql.sample1_01();
INFO: HELLO WORLD！！
sample1_01
------------
(1 row)
ファンクションの削除
DROP ファンクション名();
DROP ファンクション名(引数の型);
DROP ファンクション名(引数名 引数の型);
で削除することが可能です。
同一名のファンクションが他になければDROP ファンクション名();で問題ありませんが、
引数の定義が異なるファンクションが複数存在する場合は、明示的に型を指定する必要があります。
例） CHAR型の引数を持つ関数を削除する場合
example-db=> DROP FUNCTION test_plpgsql.sample1_01(CHAR);
DROP FUNCTION
プロシージャについて
PL/pgSQLにはファンクションだけでなくプロシージャも存在します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample1_02() AS $$
DECLARE
BEGIN
RAISE INFO 'HELLO WORLD！！';
END;
$$ LANGUAGE plpgsql;
プロシージャの作成
CREATE OR REPLACE PROCEDURE 関数名(引数)
で作成することができます。
プロシージャの実行
CALL プロシージャ名(引数);で実行することが可能です
実行結果
example-db=> CALL test_plpgsql.sample1_02();
INFO: HELLO WORLD！！
CALL
ファンクションとプロシージャの違い
ファンクションとプロシージャの主な違いは以下が挙げられます。
-
戻り値について
- ファンクションは戻り値の指定が可能
- プロシージャは戻り値の指定が不可
-
実行方法について
- ファンクションはSQLで実行する
- プロシージャはCALL文で実行する
-
トランザクション制御について
- ファンクションはCOMMITおよびROLLBACKコマンドを用いたトランザクションの制御が不可
- プロシージャはCOMMITおよびROLLBACKコマンドを用いたトランザクションの制御が可能
関数本文について
PL/pgSQLはブロック構造の言語であり、
DECLARE〜BEGIN〜EXCEPTION〜END;で囲われたブロックで構成されています。
CREATE OR REPLACE FUNCTION test_plpgsql.sample1_01() RETURNS VOID AS $$
DECLARE
-- 変数の宣言を記載する
BEGIN
-- 手続き内容を記載する
EXCEPTION
-- 例外処理を記載する
END; -- PL/pgSQLのブロックの終了
$$ LANGUAGE plpgsql; --言語を指定する
DECLARE
関数本文で利用する変数を宣言します。
BEGIN
実際に行う処理を記載します。
EXCEPTION
関数本文内でエラーが発生した際、EXCEPTION内に処理が移るため、
EXCEPTION内でエラーハンドリングに関する処理を記載します。
END;
PL/pgSQLのブロックの終了を示します。
DECLARE/BEGIN/EXCEPTIONと異なり、末尾に;（セミコロン）をつける必要があります。
AS $$ 〜 $$
AS以降に関数を定義する文字列定数を記載します。
シングルクォーテーション（'）で囲うことも可能ですが、関数内でシングルクォーテーション利用する場合、エスケープする必要があります。
シングルクォーテーション（'）で囲う場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample1_02() AS '
DECLARE
BEGIN
RAISE INFO ''HELLO WORLD！！'';
END;
' LANGUAGE plpgsql;
ドル引用符付けと呼ばれる$$で囲うことで、シングルクォーテーションをエスケープすることなく使用することが可能です。
LANGUAGE plpgsql;
LANGUAGE以降に関数を実装している言語の名前を記載します。
今回はplpgsqlを指定しますが、それ以外にsql、c、internalが指定可能です。
副ブロックについて
DECLARE/BEGIN/EXCEPTIONブロックは、副ブロックとして入れ子の形式で囲うことが可能です。
副ブロック内で外側のブロックと同名の変数を宣言可能ですが、副ブロック内で外側のブロックを参照する際は
外側のブロックの先頭に<< ラベル名 >>を記載して、ラベル名.変数名で参照することが可能です。
CREATE OR REPLACE PROCEDURE test_plpgsql.sample2(addnum numeric) AS $$
<< outerblock >>
DECLARE
num1 numeric := 30;
BEGIN
RAISE INFO '[ブロックの外側] num1の値：%',num1;
-- 副ブロックの開始 ------------------------
DECLARE
num1 numeric := 50;
BEGIN
RAISE INFO '[ブロックの内側] num1の値：%',num1;
RAISE INFO '[ブロックの外側] num1の値：%',outerblock.num1;
END;
-- 副ブロックの終了 ------------------------
num1 := num1 + addnum;
RAISE INFO '[ブロックの外側] num1の値：%',num1;
RAISE INFO '[ブロックの外側] num1の値：%',outerblock.num1;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample2_01(10);
INFO: [ブロックの外側] num1の値：30
INFO: [ブロックの内側] num1の値：50
INFO: [ブロックの外側] num1の値：30
INFO: [ブロックの外側] num1の値：40
INFO: [ブロックの外側] num1の値：40
CALL
宣言
変数の宣言方法について説明します。
変数 / 定数 / デフォルト値について
変数
変数名 データ型
の形式で記載します。
変数の初期値を設定する場合は:=を用いて変数名 データ型 := 初期値の形式で記載します。
定数
変数名 CONSTANT データ型
の形式で記載します。
CONSTANTがついた変数は、初期化後の代入は不可となります。
デフォルト値
変数名 データ型 DEFAULT 初期値
の形式で記載します。
DEFAULTがついた変数は、ブロックに入った時に指定した初期値が変数に代入されます。
NOT NULL
変数名 データ型 NOT NULL
の形式で記載します。
NOT NULLがついた変数は、NULLの代入は不可となります。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_01() AS $$
DECLARE
-- 変数
url VARCHAR := 'https://www.postgresql.jp/document/';
-- 定数
user_id CONSTANT INTEGER := 10;
-- NOT NULL / デフォルト値
-- NOT NULLの場合に初期値を設定しない場合はエラーとなる
-- tax NUMERIC(3,2) NOT NULL;
-- [出力結果]: ERROR: variable "tax" must have a default value, since it's declared NOT NULL
tax NUMERIC(3,2) NOT NULL DEFAULT 1.08;
BEGIN
RAISE INFO '[変更前]urlの値：%',url;
url = 'https://www.postgresql.jp/document/13/html/';
RAISE INFO '[変更後]urlの値：%',url;
RAISE INFO 'user_idの値：%',user_id;
-- 定数に代入した場合はエラーとなる
-- user_id := 11;
-- [出力結果]:variable "user_id" is declared CONSTANT
RAISE INFO '[変更前]taxの値：%',tax;
-- NOT NULLの変数にNULLを代入するとエラーになる
-- tax = NULL;
-- [出力結果]:vnull value cannot be assigned to variable "tax" declared NOT NULL
tax = 1.10;
RAISE INFO '[変更後]taxの値：%',tax;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_01();
INFO: [変更前]urlの値：https://www.postgresql.jp/document/
INFO: [変更後]urlの値：https://www.postgresql.jp/document/13/html/
INFO: user_idの値：10
INFO: [変更前]taxの値：1.08
INFO: [変更後]taxの値：1.10
CALL
%TYPE / %ROWTYPEについて
%TYPE
変数名 テーブル名.カラム名%TYPE
の形式で記載することで、関数作成時にテーブルのデータ型を参照して該当のカラムと同一の型が定義されます。
%ROWTYPE
変数名 テーブル名%ROWTYPE
の形式で記載することで、関数作成時に指定したテーブルの列定義を持つ複合型が定義されます。
複合型は、SELECTの問い合わせの結果を保持する際などで利用されます。
また、変数名.列名で参照することが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_02() AS $$
DECLARE
-- 型のコピー %TYPE
myfield test_plpgsql.dept.deptno%TYPE; -- deptテーブルのdeptnoカラムはcharacter型のため、character型となる
-- 行型 %ROWTYPE
myrow test_plpgsql.member%ROWTYPE;
BEGIN
-- 型のコピー %TYPE
SELECT deptno INTO myfield FROM test_plpgsql.dept ORDER BY deptno LIMIT 1;
RAISE INFO 'myfieldの値:%, myfieldの型:%', myfield, pg_typeof(myfield);
-- 行型 %ROWTYPE
SELECT * INTO myrow FROM test_plpgsql.member ORDER BY deptno LIMIT 1;
RAISE INFO 'myrow.deptnoの値:%, myrow.empnoの値:%', myrow.deptno, myrow.empno;
END;
$$ LANGUAGE plpgsql;
example-db=> CALL test_plpgsql.sample3_02();
INFO: myfieldの値:0 , myfieldの型:character
INFO: myrow.deptnoの値:0 , myrow.empnoの値:1
CALL
RECORD型（複合型）について
RECORD型
変数名 RECORD
の形式で記載します。
%ROWTYPEと似た複合型ですが、%ROWTYPEと異なる点として、宣言時に具体的な列のデータは保持しておらず、SELECTなどで値が代入されたタイミングで実際の列定義が決まります。
RECODE型変数に格納された列のデータに関しては、変数名.列名で参照することが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_03() AS $$
DECLARE
arow RECORD;
BEGIN
FOR arow IN SELECT * FROM test_plpgsql.dept ORDER BY deptno LOOP
RAISE INFO 'arow.deptnoの値:%, arow.deptnameの値:%', arow.deptno, arow.deptname;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_03();
INFO: arow.deptnoの値:0 , arow.deptnameの値:営業部
INFO: arow.deptnoの値:1 , arow.deptnameの値:開発部
INFO: arow.deptnoの値:2 , arow.deptnameの値:人事部
CALL
複合型のユーザ定義について
ユーザ定義型はcreate type ユーザ定義名 as ...で作成されたデータ型ですが、
詳細は以下のリンクを参照してください。
複合型のユーザ定義
変数名 複合型のユーザ定義
の形式で記載します。
複合型のユーザ定義に格納された属性のデータに関しては、変数名.属性名で参照することが可能です。
サンプルコード
複合型のユーザ定義
-- 複合型のユーザ定義
create type test_plpgsql.DATA_TYPE1 as (
param1 numeric(1,0)
,param2 text
,param3 bytea
);
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_04_01() AS $$
DECLARE
-- ユーザ定義型の宣言
dataType1 test_plpgsql.DATA_TYPE1;
BEGIN
-- 値の設定
dataType1.param1 := 1;
dataType1.param2 := 'DATA_TYPE1';
dataType1.param3 := 'DATA_TYPE1'::bytea;
-- 値の参照
RAISE INFO '--ユーザ定義の値確認！！！！--';
RAISE INFO 'dataType1:%', dataType1;
RAISE INFO 'dataType1.param1:%', dataType1.param1;
RAISE INFO 'dataType1.param2:%', dataType1.param2;
RAISE INFO 'dataType1.param3:%', dataType1.param3;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_04_01();
INFO: --ユーザ定義の値確認！！！！--
INFO: dataType1:(1,DATA_TYPE1,"\\x444154415f5459504531")
INFO: dataType1.param1:1
INFO: dataType1.param2:DATA_TYPE1
INFO: dataType1.param3:\x444154415f5459504531
CALL
入れ子になっている複合型のユーザ定義
以下の例の通り、ユーザ定義の属性に別のユーザ定義型が定義することが可能です。
create type ユーザ定義名1 as (
属性名1 numeric(1,0)
,属性名2 ユーザ定義名2
);
上記の場合、値を参照する際は
(ユーザ定義名1.属性名2).ユーザ定義名2の属性名
の形式で記載します。
サンプルコード
複合型のユーザ定義
-- 複合型のユーザ定義
create type test_plpgsql.DATA_TYPE1 as (
param1 numeric(1,0)
,param2 text
,param3 bytea
);
-- 複合型のユーザ定義型(入れ子パターン)
create type test_plpgsql.DATA_TYPE2 as (
param1 numeric(1,0)
,param2 test_plpgsql.DATA_TYPE1
);
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_04_02() AS $$
DECLARE
-- ユーザ定義型の宣言
dataType1 test_plpgsql.DATA_TYPE1;
dataType2 test_plpgsql.DATA_TYPE2;
BEGIN
-- 値の設定
dataType1.param1 := 1;
dataType1.param2 := 'DATA_TYPE1';
dataType1.param3 := 'DATA_TYPE1'::bytea;
dataType2.param1 := 1;
dataType2.param2 := dataType1;
-- 入れ子のユーザ定義を設定する際、以下のやり方だとエラーになる
-- dataType2.param2.param1 := 1;
-- datatype2.param2.param1" is not a known variable
-- 値の参照
RAISE INFO '--ユーザ定義の値確認！！！！--';
RAISE INFO 'dataType2:%', dataType2;
RAISE INFO 'dataType2.param1:%', dataType2.param1;
RAISE INFO 'dataType2.param2:%', dataType2.param2;
-- 入れ子のユーザ定義の参照の仕方に注意
-- RAISE INFO 'dataType2.param2:%', dataType2.param2.param1;
-- missing FROM-clause entry for table "param2"
-- 入れ子のユーザ定義を参照したい場合は以下のように()を利用する
RAISE INFO '(dataType2.param2).param1:%', (dataType2.param2).param1;
RAISE INFO '(dataType2.param2).param2:%', (dataType2.param2).param2;
RAISE INFO '(dataType2.param2).param2:%', (dataType2.param2).param3;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_04_02();
INFO: --ユーザ定義の値確認！！！！--
INFO: dataType2:(1,"(1,DATA_TYPE1,""\\\\x444154415f5459504531"")")
INFO: dataType2.param1:1
INFO: dataType2.param2:(1,DATA_TYPE1,"\\x444154415f5459504531")
INFO: (dataType2.param2).param1:1
INFO: (dataType2.param2).param2:DATA_TYPE1
INFO: (dataType2.param2).param2:\x444154415f5459504531
CALL
配列について
配列の扱いに関しては、以下のサイトを参考にしています。
配列の宣言
変数名 データ型[]
の形式で記載します。
配列の初期化
変数名 := ARRAY[値1,値2...]
の形式で配列を初期化することができます。
配列の値の代入
変数名[添字] := 値
の形式で配列の各要素に値を代入することができます。
サンプルコード
パターン1：スカラ型の配列
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_05_01() AS $$
DECLARE
-- 配列の宣言 スカラ型
arr1 INT[];
arr2 INT[];
-- %TYPEを用いて配列を作成することは不可
-- arr2 test_plpgsql.dept.deptno%type[];
-- SQLエラー [42601]: ERROR: syntax error at or near "["
BEGIN
-- 配列の値の設定方法
-- 方法①：配列コンストラクタを利用するやり方
arr1 := array[123, 456, 789];
-- 方法②：添字を設定して直接代入するやり方
arr2[1] := 1;
arr2[2] := 2;
arr2[3] := 3;
RAISE INFO '--配列の値確認！！！！--';
RAISE INFO 'arr1[1]:%', arr1[1];
RAISE INFO 'arr1[2]:%', arr1[2];
RAISE INFO 'arr1[3]:%', arr1[3];
RAISE INFO 'arr2[1]:%', arr2[1];
RAISE INFO 'arr2[2]:%', arr2[2];
RAISE INFO 'arr2[3]:%', arr2[3];
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_05_01();
INFO: --配列の値確認！！！！--
INFO: arr1[1]:123
INFO: arr1[2]:456
INFO: arr1[3]:789
INFO: arr2[1]:1
INFO: arr2[2]:2
INFO: arr2[3]:3
CALL
パターン2：複合型のユーザ定義の配列
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_05_02() AS $$
DECLARE
-- 配列の宣言 ユーザ定義型
dataType test_plpgsql.DATA_TYPE1;
arrDataType test_plpgsql.DATA_TYPE1[];
BEGIN
-- 値の設定
dataType.param1 := 1;
dataType.param2 := 'DATA_TYPE1';
dataType.param3 := 'DATA_TYPE1'::bytea;
arrDataType[1] := dataType;
dataType.param1 := 2;
dataType.param2 := 'DATA_TYPE2';
dataType.param3 := 'DATA_TYPE2'::bytea;
arrDataType[2] := dataType;
-- 配列[添字].ユーザ定義の項目名 といった記載の仕方はエラーとなる
-- arrDataType[2].param1 := 3;
-- SQLエラー [42601]: ERROR: syntax error at or near "."
RAISE INFO 'arrDataType[1]:%', arrDataType[1];
RAISE INFO 'arrDataType[2]:%', arrDataType[2];
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample3_05_02();
INFO: arrDataType[1]:(1,DATA_TYPE1,"\\x444154415f5459504531")
INFO: arrDataType[2]:(2,DATA_TYPE2,"\\x444154415f5459504532")
CALL
関数引数の宣言について
関数引数の宣言
関数内で定義されている引数に関しては、関数本文内で$1、$2...という識別子（数字は何番目の引数であるかを示します）で参照可能ですが、別名を宣言することも可能です。
別名の宣言方法
宣言方法１
関数名(別名 型名)
の形式で記載します。
宣言方法２
DECLARE内で別名 ALIAS FOR $n;
の形式で記載します。
サンプルコード
/*
* - 第一引数：型、別名（addnum）を両方宣言
* - 第二引数：型のみ宣言
* 別名は引数ではなく、DECLARE内で宣言(subtractnum)
* [name ALIAS FOR $n;]
*/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample4_01(addnum NUMERIC, NUMERIC) AS $$
DECLARE
initnum NUMERIC := 10;
subtractnum ALIAS FOR $2;
BEGIN
RAISE INFO '[識別子]第一引数の値：%', $1;
RAISE INFO '[識別子]第二引数の値：%', $2;
RAISE INFO '[別名]第一引数の値：%', addnum;
RAISE INFO '[別名]第二引数の値：%', subtractnum;
RAISE INFO '計算結果：%', initnum + addnum - subtractnum;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample4_01(12, 3);
INFO: [識別子]第一引数の値：12
INFO: [識別子]第二引数の値：3
INFO: [別名]第一引数の値：12
INFO: [別名]第二引数の値：3
INFO: 計算結果：19
CALL
基本的な文
代入について
代入
変数名 = 値;
変数名 := 値;
の形式で記載します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample5_01() AS $$
DECLARE
user_name varchar(255);
user_id integer;
BEGIN
user_name := 'ぽすぐれ太郎';
user_id = 1;
raise INFO 'user_name:%, user_id:%', user_name, user_id;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample5_01();
INFO: user_name:ぽすぐれ太郎, user_id:1
CALL
PERFORMについて
PERFORM
PERFORM SQL文
の形式で利用で記載します。
関数の呼び出しにおいて、実行結果が不要である場合にPERFORMを使用します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample6_01() AS $$
DECLARE
myrow record;
message text;
BEGIN
-- select * from test_plpgsql.member;
-- ERROR: query has no destination for result data
-- HINT: If you want to discard the results of a SELECT, use PERFORM instead.
-- CONTEXT: PL/pgSQL function test_plpgsql.sample5_01() line 4 at SQL statement
SELECT * INTO myrow FROM pg_tables ORDER BY schemaname;
RAISE INFO 'myrowからスキーマ名とテーブル名を出力：%', myrow.schemaname || '.' || myrow.tablename;
PERFORM * from test_plpgsql.member;
-- FUNCTIONをSELECT FUNCTION名;で実行するとエラーとなる。
-- SELECT INTO または PERFORM を利用することでエラーを回避することができる。
-- SELECT test_plpgsql.sample6_01_01();
-- ERROR: query has no destination for result data
-- HINT: If you want to discard the results of a SELECT, use PERFORM instead.
SELECT * into message FROM test_plpgsql.sample6_01_01();
RAISE INFO '%', message;
PERFORM test_plpgsql.sample6_01_01();
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test_plpgsql.sample6_01_01() RETURNS TEXT AS $$
DECLARE
BEGIN
RETURN '[execute]:sample6_01_01';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample6_01();
INFO: myrowからスキーマ名とテーブル名を出力：information_schema.sql_implementation_info
INFO: [execute]:sample6_01_01
CALL
SELECT INTOについて
SELECT INTO
SELECT （カラム名｜関数の戻り値） INTO 変数名 FROM （TBL名｜関数名）
の形式で記載します。
SELECT文の結果を変数に代入したいときに利用します。
SELECT INTO STRICT
SELECT INTOにSTRICTを追加した場合、
SELECTの実行結果が0件の場合はNO_DATA_FOUND、2件以上の場合はTOO_MANY_ROWSというエラーが発生します。
サンプルコード
パターン1：レコードを１件取得する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample7_01() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO 'SELECT INTO 実行（STRICTなし）';
SELECT * INTO myrow FROM test_plpgsql.emp WHERE empno = '1';
RAISE INFO '従業員名：%', myrow.empname;
RAISE INFO 'SELECT INTO 実行（STRICTあり）';
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = '1';
RAISE INFO '従業員名：%', myrow.empname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee not found';
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee not unique';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample7_01();
INFO: SELECT INTO 実行（STRICTなし）
INFO: 従業員名：営業部長
INFO: SELECT INTO 実行（STRICTあり）
INFO: 従業員名：営業部長
CALL
パターン2：レコードを0件取得する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample7_02() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO 'SELECT INTO 実行（STRICTなし）';
SELECT * INTO myrow FROM test_plpgsql.emp WHERE empno = '0';
RAISE INFO '従業員名：%', myrow.empname;
RAISE INFO 'SELECT INTO 実行（STRICTあり）';
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = '0';
RAISE INFO '従業員名：%', myrow.empname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee not found';
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee not unique';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample7_02();
INFO: SELECT INTO 実行（STRICTなし）
INFO: 従業員名：<NULL>
INFO: SELECT INTO 実行（STRICTあり）
ERROR: employee not found
CONTEXT: PL/pgSQL function sample7_02() line 15 at RAISE
パターン3：レコードを2件以上取得する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample7_03() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO 'SELECT INTO 実行（STRICTなし）';
SELECT * INTO myrow FROM test_plpgsql.emp WHERE poscode = '1';
RAISE INFO '従業員名：%', myrow.empname;
RAISE INFO 'SELECT INTO 実行（STRICTあり）';
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE poscode = '1';
RAISE INFO '従業員名：%', myrow.empname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee not found';
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee not unique';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample7_03();
INFO: SELECT INTO 実行（STRICTなし）
INFO: 従業員名：営業課長1
INFO: SELECT INTO 実行（STRICTあり）
ERROR: employee not unique
CONTEXT: PL/pgSQL function sample7_03() line 17 at RAISE
INSERT / UPDATE / DELETE RETURNINGについて
INSERT / UPDATE / DELETE RETURNING
INSERT文 RETURNING カラム名 INTO 変数名;
UPDATE文 RETURNING カラム名 INTO 変数名;
DELETE文 RETURNING カラム名 INTO 変数名;
の形式で記載します。
SELECT INTOと同様に、SQLの実行結果を変数に格納します。
更新件数が2件以上ある場合は、TOO_MANY_ROWSエラーが発生します。
また、INTOの直後にSTRICTを追加した場合、実行結果が0件の場合にNO_DATA_FOUNDエラーが発生します。
INSERT / UPDATE / DELETE RETURNINGについて
サンプルコード
パターン1:STRICT無し / 更新件数２件
CREATE OR REPLACE PROCEDURE test_plpgsql.sample8_01() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO 'UPDATE RETURNING 実行（STRICTなし／更新件数1件）';
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1002' RETURNING * INTO myrow;
RAISE INFO '従業員No.：% 従業員名：% 役職コード：% 年齢：%', myrow.empno, myrow.empname, myrow.poscode, myrow.age;
RAISE INFO 'UPDATE RETURNING 実行（STRICTなし／更新件数2件）';
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1001' OR empno = '1002' RETURNING * INTO myrow;
RAISE INFO '従業員No.：% 従業員名：% 役職コード：% 年齢：%', myrow.empno, myrow.empname, myrow.poscode, myrow.age;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee not found';
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee not unique';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample8_01();
INFO: UPDATE RETURNING 実行（STRICTなし／更新件数1件）
INFO: 従業員No.：1002 従業員名：人事社員30001 役職コード：3 年齢：30
INFO: UPDATE RETURNING 実行（STRICTなし／更新件数2件）
ERROR: employee not unique
CONTEXT: PL/pgSQL function sample8_01() line 16 at RAISE
パターン2:STRICT有り / 更新件数0件
CREATE OR REPLACE PROCEDURE test_plpgsql.sample8_02() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO 'UPDATE RETURNING 実行（STRICTあり／更新件数1件）';
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1002' RETURNING * INTO STRICT myrow;
RAISE INFO '従業員No.：% 従業員名：% 役職コード：% 年齢：%', myrow.empno, myrow.empname, myrow.poscode, myrow.age;
RAISE INFO 'UPDATE RETURNING 実行（STRICTあり／更新件数0件）';
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1003' RETURNING * INTO STRICT myrow;
RAISE INFO '従業員No.：% 従業員名：% 役職コード：% 年齢：%', myrow.empno, myrow.empname, myrow.poscode, myrow.age;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee not found';
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee not unique';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample8_02();
INFO: UPDATE RETURNING 実行（STRICTあり／更新件数1件）
INFO: 従業員No.：1002 従業員名：人事社員30001 役職コード：3 年齢：30
INFO: UPDATE RETURNING 実行（STRICTあり／更新件数0件）
ERROR: employee not found
CONTEXT: PL/pgSQL function sample8_02() line 14 at RAISE
EXECUTEについて
EXECUTE
実行する度に別のテーブルや別のデータ型を使用するなど、動的にSQLコマンドを実行したい場合に利用します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample9_01(numeric, text) AS $$
DECLARE
myrow record;
BEGIN
EXECUTE 'CREATE TEMPORARY TABLE IF NOT EXISTS sample11 (col1 numeric, col2 text);';
EXECUTE 'TRUNCATE TABLE sample11';
EXECUTE 'INSERT INTO sample11 (col1, col2) VALUES ($1, $2);' USING $1, $2;
EXECUTE 'SELECT * FROM sample11 WHERE col1 = $1;' INTO myrow USING $1;
RAISE INFO 'myrow.col1：% myrow.co2：%', myrow.col1, myrow.col2;
EXECUTE 'UPDATE sample11 SET col2 = ''UPDATE実施！'' WHERE col1 = ' || $1 || 'RETURNING * ;' INTO myrow ;
RAISE INFO 'col1：% col2：% ', myrow.col1, myrow.col2;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample9_01(1,'1');
INFO: myrow.col1：1 myrow.co2：1
INFO: col1：1 col2：UPDATE実施！
CALL
結果ステータスの取得について
GET DIAGNOSTICS
以下２つの情報を取得するために、GET DIAGNOSTICS を利用します。
- 最後のSQLコマンドにより処理された行数
- 現在の呼び出しスタックを記述したテキストの行
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample10_01() AS $$
DECLARE
integer_var1 INTEGER;
stack text;
BEGIN
PERFORM * FROM test_plpgsql.emp;
-- test_plpgsql.empテーブルのレコード件数を取得する
GET CURRENT DIAGNOSTICS integer_var1 = ROW_COUNT;
RAISE INFO '%', integer_var1;
-- 現在の呼び出しスタックを記述したテキストの行
GET DIAGNOSTICS stack = PG_CONTEXT;
RAISE INFO '%', stack;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample10_01();
INFO: 1002
INFO: PL/pgSQL function sample10_01() line 11 at GET DIAGNOSTICS
CALL
FOUND
FOUNDはboolean型の変数で、デフォルトはfalseとなっていますが、
PL/pgSQLの各関数呼び出し後の条件により値がtrueになる特殊な変数です。
| 構文 | trueになる条件 |
|---|---|
| SELECT INTO | 行が代入された場合 |
| PERFORM | 1つ以上の行が生成（破棄）された場合 |
| UPDATE / INSERT / DELETE | 少なくとも1行が影響を受けた場合 |
| FETCH | 行が返された場合 |
| MOVE | カーソルの移動が成功した場合 |
| FOR文 / FOREACH | 1回以上繰り返しが行われた場合 |
| RETURN QUERY / RETURN QUERY EXECUTE | 行を１つでも返却した場合 |
サンプルコード
SELECT INTOの場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_01() AS $$
DECLARE
myrow record;
BEGIN
RAISE INFO '%', FOUND;
SELECT * INTO myrow FROM test_plpgsql.emp WHERE poscode = '1';
RAISE INFO '%', FOUND;
IF FOUND THEN
RAISE INFO 'レコードが存在します！';
END IF;
SELECT * INTO myrow FROM test_plpgsql.emp WHERE empno = '0';
RAISE INFO '%', FOUND;
IF NOT FOUND THEN
RAISE INFO 'レコードが存在しません！';
END IF;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample11_01();
INFO: f
INFO: t
INFO: レコードが存在します！
INFO: f
INFO: レコードが存在しません！
CALL
PERFORMの場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_02() AS $$
DECLARE
BEGIN
RAISE INFO '%', FOUND;
PERFORM * FROM test_plpgsql.emp WHERE poscode = '1';
RAISE INFO '%', FOUND;
IF FOUND THEN
RAISE INFO 'レコードが存在します！';
END IF;
PERFORM * FROM test_plpgsql.emp WHERE empno = '0';
RAISE INFO '%', FOUND;
IF NOT FOUND THEN
RAISE INFO 'レコードが存在しません！';
END IF;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample11_02();
INFO: f
INFO: t
INFO: レコードが存在します！
INFO: f
INFO: レコードが存在しません！
CALL
UPDATEの場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_03() AS $$
DECLARE
BEGIN
RAISE INFO '%', FOUND;
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1002';
RAISE INFO '%', FOUND;
IF FOUND THEN
RAISE INFO 'レコードが存在します！';
END IF;
UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1003';
RAISE INFO '%', FOUND;
IF NOT FOUND THEN
RAISE INFO 'レコードが存在しません！';
END IF;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample11_03();
INFO: f
INFO: t
INFO: レコードが存在します！
INFO: f
INFO: レコードが存在しません！
CALL
FETCHの場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_04(VARCHAR) AS $$
DECLARE
empnames CURSOR (pPosCode VARCHAR) FOR
SELECT empname FROM test_plpgsql.emp WHERE poscode = pPosCode;
empname varchar(40);
BEGIN
RAISE INFO '%', FOUND;
OPEN empnames($1);
LOOP
FETCH empnames INTO empname;
RAISE INFO '%', FOUND;
IF FOUND THEN
RAISE INFO 'レコードが存在します！';
ELSE
RAISE INFO 'レコードが存在しません！';
EXIT;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample11_04('1');
INFO: f
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: t
INFO: レコードが存在します！
INFO: f
INFO: レコードが存在しません！
CALL
MOVEの場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_05(VARCHAR) AS $$
DECLARE
poanames CURSOR (pPosCode VARCHAR) FOR
SELECT posname FROM test_plpgsql.pos WHERE poscode = pPosCode;
poaname varchar(20);
BEGIN
RAISE INFO '%', FOUND;
OPEN poanames($1);
MOVE FORWARD 4 IN poanames;
RAISE INFO '%', FOUND;
MOVE FORWARD 4 IN poanames;
RAISE INFO '%', FOUND;
FETCH poanames INTO poaname;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample11_05('1');
INFO: f
INFO: t
INFO: f
CALL
RETURN QUERYの場合
CREATE OR REPLACE FUNCTION test_plpgsql.sample11_06(VARCHAR)
RETURNS TABLE (
p_poscode char(1),
p_posname varchar(20)
) AS $$
DECLARE
BEGIN
RAISE INFO '%', FOUND;
RETURN QUERY SELECT * FROM test_plpgsql.pos WHERE poscode = $1;
RAISE INFO '%', FOUND;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample11_06('1');
INFO: f
INFO: t
sample11_06
-------------
(1,課長)
(1 row)
制御構造
IN / INOUT / OUTパラメータモードについて
関数の引数にはIN、OUT、INOUTという３つのパラメータモードを指定することができます。
例）
FUNCTION名(変数名 IN 型)
FUNCTION名(変数名 IN OUT 型)
FUNCTION名(変数名 OUT 型)
INパラメータ
呼び出し元から値を受けとるパラメータです。
パラメータモードの指定がない場合のデフォルトはINとなります。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample12_01(name IN VARCHAR(40)) AS $$
DECLARE
empAge NUMERIC(3,0);
BEGIN
SELECT age INTO empAge FROM test_plpgsql.emp WHERE empname = name;
RAISE INFO '%さんの年齢は%歳です', name ,empAge;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample12_01('営業部長');
INFO: 営業部長さんの年齢は45歳です
CALL
OUTパラメータ
呼び出し元へ値を返却するパラメータです。
引数内で宣言していますが、呼び出し元から値が渡されることはなく、関数内で戻り値として利用しているイメージです。
関数本文でRETURN文で処理が終了したタイミングでのOUTパラメータの値が返却されます。
注意
プロシージャはOUTパラメータを利用することができません
サンプルコード
OUTパラメータが１つの場合
- 戻り値の型が
OUTパラメータで指定した型となります。（戻り値を指定しなくてもOK）
CREATE OR REPLACE FUNCTION test_plpgsql.sample12_02(name IN VARCHAR(40), empAge OUT NUMERIC(3,0)) AS $$
DECLARE
BEGIN
SELECT age INTO empAge FROM test_plpgsql.emp WHERE empname = name;
RAISE INFO '%さんの年齢は%歳です', name ,empAge;
-- RETURNの際にOUTパラメータを指定するとエラーとなる
-- [ERROR] RETURN cannot have a parameter in function with OUT parameters
-- RETURN empAge;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample12_02('営業部長');
INFO: 営業部長さんの年齢は45歳です
sample12_02
-------------
45
(1 row)
OUTパラメータが2つ以上の場合
- 戻り値の型が
RECODE型となります。（戻り値を指定しなくてもOK）
CREATE OR REPLACE FUNCTION test_plpgsql.sample12_03(name IN VARCHAR(40), empAge OUT NUMERIC(3,0), result OUT NUMERIC(1,0))
RETURNS RECORD AS $$
DECLARE
BEGIN
result := 0;
SELECT age INTO empAge FROM test_plpgsql.emp WHERE empname = name;
RAISE INFO '%さんの年齢は%歳です', name ,empAge;
RETURN;
EXCEPTION
WHEN OTHERS THEN
result := -1;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample12_03('営業部長');
INFO: 営業部長さんの年齢は45歳です
sample12_03
-------------
(45,0)
(1 row)
INOUTパラメータ
INパラメータ、OUTパラメータ両方の役割を持ったパラメータモードです。
呼び出し元から値を受けとり、最終的に呼び出し元へ値を返却します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample12_04(empAge IN NUMERIC(3,0)) AS $$
DECLARE
empNumber NUMERIC(5,0);
BEGIN
SELECT count INTO empNumber FROM test_plpgsql.sample12_04_01(empAge, empNumber);
RAISE INFO '%歳以上の社員は%人です', empAge ,empNumber;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test_plpgsql.sample12_04_01(empAge IN NUMERIC(3,0), count IN OUT NUMERIC(5,0)) AS $$
DECLARE
BEGIN
RAISE INFO 'COUNTの値：%', count;
SELECT COUNT(*) INTO count FROM test_plpgsql.emp WHERE age >= empAge;
RAISE INFO 'COUNTの値：%', count;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample12_04(45);
INFO: COUNTの値：<NULL>
INFO: COUNTの値：3
INFO: 45歳以上の社員は3人です
CALL
RETURNについて
RETURN
関数の処理を終了させる、呼び出し元に値を返却させるために利用します。
サンプルコード
戻り値宣言が存在する場合
CREATE OR REPLACE FUNCTION test_plpgsql.sample13_01(name IN VARCHAR(40)) RETURNS NUMERIC(3,0) AS $$
DECLARE
empAge NUMERIC(3,0);
BEGIN
SELECT age INTO empAge FROM test_plpgsql.emp WHERE empname = name;
RETURN empAge;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample13_01('営業部長') AS 年齢;
年齢
------
45
(1 row)
引数にOUTパラメータが存在する場合
CREATE OR REPLACE FUNCTION test_plpgsql.sample13_02(name IN VARCHAR(40), empAge OUT NUMERIC(3,0)) RETURNS NUMERIC(3,0) AS $$
DECLARE
BEGIN
SELECT age INTO empAge FROM test_plpgsql.emp WHERE empname = name;
RAISE INFO '%さんの年齢は%歳です', name ,empAge;
-- RETURNの際にOUTパラメータを指定するとエラーとなる
-- [ERROR] RETURN cannot have a parameter in function with OUT parameters
-- RETURN empAge;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample13_02('営業部長');
INFO: 営業部長さんの年齢は45歳です
sample13_02
-------------
45
(1 row)
戻り値宣言 ／ OUTパラメータが存在しない場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample13_03(empAge IN NUMERIC(3,0)) AS $$
DECLARE
empNumber NUMERIC(5,0);
BEGIN
IF empAge < 0 THEN
-- 入力値が０未満の場合はSELECTを実行せずに処理を終了する
RAISE INFO '年齢が0歳未満です';
RETURN;
END IF;
SELECT COUNT(*) INTO empNumber FROM test_plpgsql.emp WHERE age >= empAge;
RAISE INFO '%歳以上の社員は%人です', empAge ,empNumber;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample13_03(45);
INFO: 45歳以上の社員は3人です
CALL
example-db=> CALL test_plpgsql.sample13_03(-1);
INFO: 年齢が0歳未満です
CALL
RETURN NEXT / RETURN QUERY / RETURN QUERY EXECUTEについて
関数の戻り値にはSETOF sometypeやTABLE(columns)を指定することができます。
例）
FUNCTION 関数名() RETURNS SETOF sometype
FUNCTION 関数名() RETURNS TABLE(columns)
これは集合（複数の行）を呼び出し元に返却する際に宣言しますが、
戻り値の個々の項目を返却する際に、RETURN NEXT、 RETURN QUERY文を用います。
RETURN NEXT
RETURN NEXT 戻り値で宣言したデータ型
と記載することで、その列を戻り値として返すことができます。
注意
RETURNと異なり、RETURN NEXTが実行されたタイミングで関数の処理が終了する、呼び出し元に値を返却するわけではありません。
サンプルコード
SETOF sometypeの場合
CREATE OR REPLACE FUNCTION test_plpgsql.sample14_01() RETURNS SETOF test_plpgsql.pos AS $$
DECLARE
myrow test_plpgsql.pos%ROWTYPE;
BEGIN
FOR myrow IN SELECT * FROM test_plpgsql.pos LOOP
RETURN NEXT myrow; -- SELECTの現在の行を返します
END LOOP;
-- RETURNで処理を終了し、ループの回数分の行を呼び元に値を返却します
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample14_01();
sample14_01
-------------
(0,部長)
(1,課長)
(2,主任)
(3,社員)
(4 rows)
TABLE(columns)の場合
CREATE OR REPLACE FUNCTION test_plpgsql.sample14_02(INTEGER)
RETURNS TABLE (
empno char(5),
empname varchar(40),
poscode char(1),
age numeric(3,0)
) AS $$
DECLARE
myrow test_plpgsql.emp%ROWTYPE;
BEGIN
FOR myrow IN SELECT * FROM test_plpgsql.emp ORDER BY empno LIMIT $1 LOOP
empno := myrow.empno;
empname := myrow.empname;
poscode := myrow.poscode;
age := myrow.age;
RETURN NEXT; -- SELECTの現在の行を返します
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample14_02(5);
sample14_02
----------------------------
("1 ",営業部長,0,45)
("10 ",人事課長1,1,40)
("100 ",人事主任28,2,35)
("1000 ",人事社員298,3,30)
("1001 ",人事社員299,3,30)
(5 rows)
RETURN QUERY
RETURN QUERY SQL文
と記載することで、SQL文の結果を返すことができます。
注意
RETURNと異なり、RETURN QUERYが実行されたタイミングで関数の処理が終了する、呼び出し元に値を返却するわけではありません。
サンプルコード
CREATE OR REPLACE FUNCTION test_plpgsql.sample15_01(CHAR) RETURNS SETOF test_plpgsql.emp AS $$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM test_plpgsql.emp WHERE empno = $1;
IF NOT FOUND THEN
RAISE EXCEPTION '社員が存在しません empno:%.', $1;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample15_01('1');
sample15_01
-------------------------
("1 ",営業部長,0,45)
(1 row)
example-db=> SELECT test_plpgsql.sample15_01('0');
ERROR: 社員が存在しません empno:0.
CONTEXT: PL/pgSQL function sample15_01(character) line 7 at RAISE
RETURN QUERY EXECUTE
RETURN QUERY EXECUTE SQL文
と記載することで、その列を戻り値として返すことができます。
サンプルコード
CREATE OR REPLACE FUNCTION test_plpgsql.sample16_01(CHAR) RETURNS SETOF test_plpgsql.emp AS $$
DECLARE
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM test_plpgsql.emp WHERE empno = $1' USING $1;
IF NOT FOUND THEN
RAISE EXCEPTION '社員が存在しません empno:%.', $1;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample16_01('1');
sample16_01
-------------------------
("1 ",営業部長,0,45)
(1 row)
example-db=> SELECT test_plpgsql.sample16_01('0');
ERROR: 社員が存在しません empno:0.
CONTEXT: PL/pgSQL function sample16_01(character) line 7 at RAISE
条件分岐について
IF文
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample17_01(CHAR) AS $$
DECLARE
myrow test_plpgsql.emp%ROWTYPE;
BEGIN
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = $1;
IF myrow.age >= 45 THEN
RAISE INFO '%はベテラン社員', myrow.empname;
ELSIF myrow.age > 30 THEN
RAISE INFO '%は中堅社員', myrow.empname;
ELSE
RAISE INFO '%は若手社員', myrow.empname;
END IF;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample17_01('1');
INFO: 営業部長はベテラン社員
CALL
example-db=> CALL test_plpgsql.sample17_01('100');
INFO: 人事主任28は中堅社員
CALL
example-db=> CALL test_plpgsql.sample17_01('1000');
INFO: 人事社員298は若手社員
CALL
単純CASE文
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample18_01() AS $$
DECLARE
target NUMERIC;
BEGIN
SELECT ROUND(( RANDOM() * (1 - 3) )::NUMERIC, 0) + 3 INTO target;
CASE target
WHEN 1 THEN
RAISE INFO 'グー';
WHEN 2 THEN
RAISE INFO 'チョキ';
WHEN 3 THEN
RAISE INFO 'パー';
END CASE;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample18_01();
INFO: チョキ
CALL
条件付きCASE文
サンプルコード
ELSEが存在する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample18_02(CHAR) AS $$
DECLARE
myrow test_plpgsql.emp%ROWTYPE;
BEGIN
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = $1;
CASE
WHEN myrow.age >= 45 THEN
RAISE INFO '%はベテラン社員', myrow.empname;
WHEN myrow.age > 35 THEN
RAISE INFO '%は中堅社員', myrow.empname;
ELSE
RAISE INFO '%は若手社員', myrow.empname;
END CASE;
END;
$$
LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample18_02('1');
INFO: 営業部長はベテラン社員
CALL
example-db=> CALL test_plpgsql.sample18_02('100');
INFO: 人事主任28は若手社員
CALL
example-db=> CALL test_plpgsql.sample18_02('1000');
INFO: 人事社員298は若手社員
CALL
ELSEが存在しない場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample18_03(CHAR) AS $$
DECLARE
myrow test_plpgsql.emp%ROWTYPE;
BEGIN
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = $1;
CASE
WHEN myrow.age >= 45 THEN
RAISE INFO '%はベテラン社員', myrow.empname;
WHEN myrow.age > 35 THEN
RAISE INFO '%は中堅社員', myrow.empname;
END CASE;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample18_03('1');
INFO: 営業部長はベテラン社員
CALL
example-db=> CALL test_plpgsql.sample18_03('100');
INFO: 人事主任28は中堅社員
CALL
example-db=> CALL test_plpgsql.sample18_03('1000');
ERROR: case not found
HINT: CASE statement is missing ELSE part.
CONTEXT: PL/pgSQL function sample18_03(character) line 6 at CASE
繰り返し処理について
LOOP
LOOP 処理内容 END LOOP;
の形式で記載します。
LOOPは、EXIT文またはRETURN文によって終了されるまで無限に繰り返される、条件なしのループです。
EXIT
EXIT WHEN 条件式
の形式で記載します。
条件を満たした場合、繰り返し処理を終了します。
CONTINUE
CONTINUE WHEN 条件式
の形式で記載します。
条件を満たした場合、繰り返し処理内のCONTINUE文以降の処理をスキップし、次の繰り返し処理を行います。
サンプルコード
EXITが存在する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample19_01() AS $$
DECLARE
i INTEGER := 0;
BEGIN
LOOP
EXIT WHEN i > 10;
IF i % 2 = 0 THEN
RAISE INFO '% ', i;
END IF;
i := i + 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample19_01();
INFO: 0
INFO: 2
INFO: 4
INFO: 6
INFO: 8
INFO: 10
CALL
CONTINUEが存在する場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample19_02() AS $$
DECLARE
i INTEGER := 0;
BEGIN
LOOP
i := i + 1;
EXIT WHEN i > 10;
CONTINUE WHEN i < 5;
RAISE INFO '% ', i;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample19_02();
INFO: 5
INFO: 6
INFO: 7
INFO: 8
INFO: 9
INFO: 10
CALL
WHILE
WHILE 条件式 LOOP 処理内容 END LOOP;
の形式で記載します。
条件式がtrueの間は繰り返し処理が行われます。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample19_03() AS $$
DECLARE
i INTEGER := 0;
BEGIN
WHILE i < 10 LOOP
RAISE INFO '% ', i;
i := i + 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample19_03();
INFO: 0
INFO: 1
INFO: 2
INFO: 3
INFO: 4
INFO: 5
INFO: 6
INFO: 7
INFO: 8
INFO: 9
CALL
FOR文
FOR 変数名 IN 下限..上限 LOOP 処理内容 END LOOP;
FOR 変数名 IN 下限..上限 BY 増減値 LOOP 処理内容 END LOOP;
FOR 変数名 IN REVERSE 上限..下限 LOOP 処理内容 END LOOP;
の形式で記載します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample19_04() AS $$
DECLARE
BEGIN
RAISE INFO '-- FOR i IN 1..5 LOOP 開始 --';
FOR i IN 1..5 LOOP
RAISE INFO '% ', i;
END LOOP;
RAISE INFO '-- FOR i IN REVERSE 5..1 LOOP 開始 --';
FOR i IN REVERSE 5..1 LOOP
RAISE INFO '% ', i;
END LOOP;
RAISE INFO '-- FOR i IN REVERSE 5..1 BY 2 LOOP 開始 --';
FOR i IN REVERSE 5..1 BY 2 LOOP
RAISE INFO '% ', i;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample19_04();
INFO: -- FOR i IN 1..5 LOOP 開始 --
INFO: 1
INFO: 2
INFO: 3
INFO: 4
INFO: 5
INFO: -- FOR i IN REVERSE 5..1 LOOP 開始 --
INFO: 5
INFO: 4
INFO: 3
INFO: 2
INFO: 1
INFO: -- FOR i IN REVERSE 5..1 BY 2 LOOP 開始 --
INFO: 5
INFO: 3
INFO: 1
CALL
FOR文（問い合わせ結果による繰り返し）
FOR 変数名 IN SQL文 LOOP 処理内容 END LOOP;
の形式で記載します。
SQLの実行結果を１行ごとに変数に代入して繰り返し処理を行います。
サンプルコード
CREATE OR REPLACE FUNCTION test_plpgsql.sample14_02(INTEGER)
RETURNS TABLE (
empno char(5),
empname varchar(40),
poscode char(1),
age numeric(3,0)
) AS $$
DECLARE
myrow test_plpgsql.emp%ROWTYPE;
BEGIN
FOR myrow IN SELECT * FROM test_plpgsql.emp ORDER BY empno LIMIT $1 LOOP
empno := myrow.empno;
empname := myrow.empname;
poscode := myrow.poscode;
age := myrow.age;
RETURN NEXT; -- SELECTの現在の行を返します
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample14_02(5);
sample14_02
----------------------------
("1 ",営業部長,0,45)
("10 ",人事課長1,1,40)
("100 ",人事主任28,2,35)
("1000 ",人事社員298,3,30)
("1001 ",人事社員299,3,30)
(5 rows)
FOREACH
FOREACH 変数名 IN ARRAY 配列型の変数名 LOOP 処理内容 END LOOP;
の形式で記載します。
配列の要素を１つずつ変数に代入して繰り返し処理を行います。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample19_05() AS $$
DECLARE
arr1 INTEGER[];
target INTEGER;
BEGIN
-- 配列の初期化
arr1 := array[123, 456, 789];
FOREACH target IN ARRAY arr1 LOOP
RAISE INFO '%', target;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample19_05();
INFO: 123
INFO: 456
INFO: 789
CALL
例外処理について
EXCEPTIONの概要
関数本文内でエラーが発生した際、関数の処理を中断し、上位に伝播されますが、
EXCEPTIONを記載することでエラー発生時に、EXCEPTION内でエラーハンドリングをすることが可能です。
BEGIN
-- 何らかの処理
EXCEPTION
WHEN OTHERS THEN -- 例外名 または SQLSTATE を指定して、指定された例外をキャッチする
-- エラーハンドリングを行う
例外のキャッチ方法
例外のキャッチ方法は２種類存在します。
例外名を指定する方法
例
EXCEPTION
WHEN SQLSTATE '23505' THEN
SQLSTATEを指定する方法
例
EXCEPTION
WHEN NO_DATA_FOUND THEN
例外発生時の自動ロールバック
EXCEPTION内で例外をキャッチした場合、自動ロールバックが発生します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample20_01(NUMERIC) AS $$
DECLARE
myrow test_plpgsql.dept%ROWTYPE;
BEGIN
INSERT INTO test_plpgsql.dept VALUES ('3','マーケティング部');
SELECT * INTO myrow FROM test_plpgsql.dept WHERE deptno = '3';
RAISE INFO '[自動ロールバック前のデータ確認]%', myrow;
CASE $1
WHEN 1 THEN
-- 一意制約違反を発生させる
INSERT INTO test_plpgsql.dept VALUES ('0','営業部');
WHEN 2 THEN
-- RAISEを用いて意図的に例外を発生させる
RAISE EXCEPTION NO_DATA_FOUND;
END CASE;
EXCEPTION
WHEN SQLSTATE '23505' THEN -- unique_violationでも可
SELECT * INTO myrow FROM test_plpgsql.dept WHERE deptno = '3';
RAISE INFO '[自動ロールバック後のデータ確認]%', myrow;
RAISE EXCEPTION 'unique_violation';
WHEN NO_DATA_FOUND THEN
SELECT * INTO myrow FROM test_plpgsql.dept WHERE deptno = '3';
RAISE INFO '[自動ロールバック後のデータ確認]%', myrow;
RAISE EXCEPTION 'employee not found';
WHEN OTHERS THEN
RAISE EXCEPTION 'others';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample20_01(1);
INFO: [自動ロールバック前のデータ確認]("3 ",マーケティング部)
INFO: [自動ロールバック後のデータ確認](,)
ERROR: unique_violation
CONTEXT: PL/pgSQL function test_plpgsql.sample20_01(numeric) line 22 at RAISE
example-db=> CALL test_plpgsql.sample20_01(2);
INFO: [自動ロールバック前のデータ確認]("3 ",マーケティング部)
INFO: [自動ロールバック後のデータ確認](,)
ERROR: employee not found
CONTEXT: PL/pgSQL function test_plpgsql.sample20_01(numeric) line 26 at RAISE
エラーに関する情報の取得
GET STACKED DIAGNOSTICSコマンドで、EXCEPTION内で例外発生時の詳細情報を取得可能です
| 名前 | 型 | 説明 |
|---|---|---|
| RETURNED_SQLSTATE | text | 例外のSQLSTATEエラーコード |
| COLUMN_NAME | text | 例外に関する列名 |
| CONSTRAINT_NAME | text | 例外に関する制約名 |
| PG_DATATYPE_NAME | text | 例外に関するデータ型名 |
| MESSAGE_TEXT | text | 例外の主要なメッセージのテキスト |
| TABLE_NAME | text | 例外に関するテーブル名 |
| SCHEMA_NAME | text | 例外に関するスキーマ名 |
| PG_EXCEPTION_DETAIL | text | 例外の詳細なメッセージのテキスト、存在する場合 |
| PG_EXCEPTION_HINT | text | 例外のヒントとなるメッセージのテキスト、存在する場合 |
| PG_EXCEPTION_CONTEXT | text | 例外時における呼び出しスタックを記述するテキストの行 |
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample20_02() AS $$
DECLARE
text_var1 TEXT;
text_var2 TEXT;
text_var3 TEXT;
text_var4 TEXT;
text_var5 TEXT;
text_var6 TEXT;
text_var7 TEXT;
text_var8 TEXT;
text_var9 TEXT;
text_var10 TEXT;
BEGIN
-- 一意制約違反を発生させる
INSERT INTO test_plpgsql.dept VALUES ('3','マーケティング部');
INSERT INTO test_plpgsql.dept VALUES ('3','マーケティング部');
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = RETURNED_SQLSTATE,
text_var2 = COLUMN_NAME,
text_var3 = CONSTRAINT_NAME,
text_var4 = PG_DATATYPE_NAME,
text_var5 = MESSAGE_TEXT,
text_var6 = TABLE_NAME,
text_var7 = SCHEMA_NAME,
text_var8 = PG_EXCEPTION_DETAIL,
text_var9 = PG_EXCEPTION_HINT,
text_var10 = PG_EXCEPTION_CONTEXT;
RAISE INFO 'RETURNED_SQLSTATE:%',text_var1;
RAISE INFO 'COLUMN_NAME:%',text_var2;
RAISE INFO 'CONSTRAINT_NAME:%',text_var3;
RAISE INFO 'PG_DATATYPE_NAME:%',text_var4;
RAISE INFO 'MESSAGE_TEXT:%',text_var5;
RAISE INFO 'TABLE_NAME:%',text_var6;
RAISE INFO 'SCHEMA_NAME:%',text_var7;
RAISE INFO 'PG_EXCEPTION_DETAIL:%',text_var8;
RAISE INFO 'PG_EXCEPTION_HINT:%',text_var9;
RAISE INFO 'PG_EXCEPTION_CONTEXT:%',text_var10;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample20_02();
INFO: RETURNED_SQLSTATE:23505
INFO: COLUMN_NAME:
INFO: CONSTRAINT_NAME:dept_pkey
INFO: PG_DATATYPE_NAME:
INFO: MESSAGE_TEXT:duplicate key value violates unique constraint "dept_pkey"
INFO: TABLE_NAME:dept
INFO: SCHEMA_NAME:test_plpgsql
INFO: PG_EXCEPTION_DETAIL:Key (deptno)=(3 ) already exists.
INFO: PG_EXCEPTION_HINT:
INFO: PG_EXCEPTION_CONTEXT:SQL statement "INSERT INTO test_plpgsql.dept VALUES ('3','マーケティング部')"
PL/pgSQL function test_plpgsql.sample20_02() line 17 at SQL statement
CALL
カーソル
カーソルについて
カーソルの役割について、公式リファレンスから引用します。
問い合わせ全体を一度に実行するのではなく、カーソルを設定して、問い合わせをカプセル化し、問い合わせの結果を一度に数行ずつ読み取ることができます。 これを行う理由の1つは、結果内に多数の行がある場合のメモリの枯渇を防ぐことです。 （しかし、PL/pgSQLユーザは通常これを心配する必要はありません。 FORループは自動的にカーソルを内部的に使用してメモリの問題を防ぐからです。） より興味深い使用方法として、呼び出し元が行を読み取ることをできるように、作成されたカーソルへの参照を返す方法があります。 これにより、関数から大量の行集合を返す際の効率が向上します。
カーソルは基本的に、以下の流れで利用されます。
1.カーソル変数の宣言
2.カーソルを開く
3.カーソルから行を取得し、変数に格納
4.カーソルを閉じる
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_01() AS $$
DECLARE
curs1 refcursor; -- ①：カーソル変数の宣言
record1 RECORD;
BEGIN
-- ②：カーソルを開く
OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
LOOP
-- ③：カーソルから行を取得し、変数に格納
FETCH curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
-- ④：カーソルを閉じる
CLOSE curs1;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_01();
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
CALL
カーソル変数の宣言 / カーソルオープンについて
カーソル変数の宣言
変数名 refcursor
変数名 CURSOR FOR SQL文
の形式で記載することでカーソル変数を宣言することが可能です。
カーソルオープン
OPEN 変数名
OPEN 変数名 FOR SQL文
OPEN 変数名 FOR EXECUTE SQL文
の形式で記載することでカーソル変数を開くことが可能です。
サンプルコード
パターン1：変数名 refcursorで宣言した場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_02(maxLength INTEGER, startPosition INTEGER) AS $$
DECLARE
curs1 refcursor;
curs2 refcursor;
record1 RECORD;
record2 RECORD;
BEGIN
-- OPEN 変数名 FOR SQL文の形式でカーソルオープン
OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
LOOP
-- カーソルから次の行を取得し、RECORD型変数に格納
FETCH curs1 INTO record1;
IF NOT FOUND THEN
-- カーソル内のレコードを全て取得した場合はループを終了
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
-- カーソルクローズ
CLOSE curs1;
-- OPEN 変数名 FOR EXECUTE SQL文の形式でカーソルオープン
OPEN curs2 FOR EXECUTE 'SELECT * FROM test_plpgsql.emp ORDER BY empno LIMIT $1 OFFSET $2;' USING maxLength, startPosition;
LOOP
-- カーソルから次の行を取得し、RECORD型変数に格納
FETCH curs2 INTO record2;
IF NOT FOUND THEN
-- カーソル内のレコードを全て取得した場合はループを終了
EXIT;
END IF;
RAISE INFO 'empno:% empname:% poscode:% age:%', record2.empno, record2.empname, record2.poscode, record2.age;
END LOOP;
-- カーソルクローズ
CLOSE curs2;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_02(3,1);
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
INFO: empno:10 empname:人事課長1 poscode:1 age:40
INFO: empno:100 empname:人事主任28 poscode:2 age:35
INFO: empno:1000 empname:人事社員298 poscode:3 age:30
CALL
パターン2：変数名 CURSOR FOR SQL文で宣言した場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_03(INTEGER, INTEGER, CHAR) AS $$
DECLARE
-- カーソル宣言（引数無し）
curs1 CURSOR FOR
SELECT * FROM test_plpgsql.emp ORDER BY empno LIMIT 3 OFFSET 1;
-- カーソル宣言（引数有り）
curs2 CURSOR (maxLength INTEGER, startPosition INTEGER) FOR
SELECT * FROM test_plpgsql.emp ORDER BY empno LIMIT maxLength OFFSET startPosition;
-- カーソル宣言（引数有り）
curs3 CURSOR (target char(5)) FOR
SELECT deptname FROM test_plpgsql.dept WHERE deptno = target;
record1 RECORD;
record2 RECORD;
deptName VARCHAR(40);
BEGIN
-- カーソルオープン（引数無し）
OPEN curs1;
RAISE INFO '--curs1 OPEN!------------------------------';
LOOP
-- カーソルから次の行を取得し、RECORD型変数に格納
FETCH curs1 INTO record1;
IF NOT FOUND THEN
-- カーソル内のレコードを全て取得した場合はループを終了
EXIT;
END IF;
RAISE INFO 'empno:% empname:% poscode:% age:%', record1.empno, record1.empname, record1.poscode, record1.age;
END LOOP;
-- カーソルクローズ
CLOSE curs1;
RAISE INFO '--curs1 CLOSE!------------------------------';
RAISE INFO '--curs2 OPEN!------------------------------';
-- カーソルオープン（引数有り）
OPEN curs2($1, $2);
LOOP
-- カーソルから次の行を取得し、RECORD型変数に格納
FETCH curs2 INTO record2;
IF NOT FOUND THEN
-- カーソル内のレコードを全て取得した場合はループを終了
EXIT;
END IF;
RAISE INFO 'empno:% empname:% poscode:% age:%', record2.empno, record2.empname, record2.poscode, record2.age;
END LOOP;
-- カーソルクローズ
CLOSE curs2;
RAISE INFO '--curs2 CLOSE!------------------------------';
RAISE INFO '--curs3 OPEN!------------------------------';
-- カーソルオープン（引数有り）
OPEN curs3(target := $3);
LOOP
-- カーソルから次の行を取得し、RECORD型変数に格納
FETCH curs3 INTO deptName;
IF NOT FOUND THEN
-- カーソル内のレコードを全て取得した場合はループを終了
EXIT;
END IF;
RAISE INFO 'deptName:%', deptName;
END LOOP;
-- カーソルクローズ
CLOSE curs3;
RAISE INFO '--curs3 CLOSE!------------------------------';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_03(3,1,'2');
INFO: --curs1 OPEN!------------------------------
INFO: empno:10 empname:人事課長1 poscode:1 age:40
INFO: empno:100 empname:人事主任28 poscode:2 age:35
INFO: empno:1000 empname:人事社員298 poscode:3 age:30
INFO: --curs1 CLOSE!------------------------------
INFO: --curs2 OPEN!------------------------------
INFO: empno:10 empname:人事課長1 poscode:1 age:40
INFO: empno:100 empname:人事主任28 poscode:2 age:35
INFO: empno:1000 empname:人事社員298 poscode:3 age:30
INFO: --curs2 CLOSE!------------------------------
INFO: --curs3 OPEN!------------------------------
INFO: deptName:人事部
INFO: --curs3 CLOSE!------------------------------
CALL
カーソルの利用について
FETCH
FETCH IN カーソル名 INTO 変数名
FETCH FROM カーソル名 INTO 変数名
の形式で記載することでSELECT INTOと同様に、カーソルから次の行を抽出し、変数に格納することが可能です。
MOVE
MOVE IN カーソル名 INTO 変数名
MOVE FROM カーソル名 INTO 変数名
の形式で記載することで、データを取り出さないでカーソルの位置を変更することが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_04() AS $$
DECLARE
curs1 refcursor;
curs2 SCROLL CURSOR FOR
SELECT * FROM test_plpgsql.dept ORDER BY deptno;
record1 RECORD;
record2 RECORD;
BEGIN
OPEN curs1 SCROLL FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
MOVE FORWARD 2 FROM curs1;
LOOP
FETCH IN curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
CLOSE curs1;
OPEN curs2;
MOVE FORWARD 2 FROM curs2;
LOOP
FETCH PRIOR IN curs2 INTO record2;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record2.deptno, record2.deptname;
END LOOP;
CLOSE curs2;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_04();
INFO: deptno:2 deptname:人事部
INFO: deptno:0 deptname:営業部
CALL
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_04_01() AS $$
DECLARE
curs1 refcursor;
record1 RECORD;
BEGIN
OPEN curs1 NO SCROLL FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
MOVE FORWARD 2 FROM curs1;
LOOP
-- NO SCROLLの場合に1つ前の行を取り出そうとするとエラーとなる
-- SQLSTATE:55000 SQLERRM:cursor can only scan forward
FETCH PRIOR IN curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
CLOSE curs1;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_04_01();
ERROR: SQLSTATE:55000 SQLERRM:cursor can only scan forward
CONTEXT: PL/pgSQL function test_plpgsql.sample21_04_01() line 21 at RAISE
UPDATE / DELETE WHERE CURRENT OF
UPDATE テーブル名 SET カラム名 = 値 WHERE CURRENT OF カーソル名
DELETE FROM テーブル名 WHERE WHERE CURRENT OF カーソル名
の形式で記載することで、カーソルによって特定した行を更新または消去することが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_04_02(beforAge INTEGER, afterAge INTEGER, maxLength INTEGER, startPosition INTEGER) AS $$
DECLARE
curs1 CURSOR (p_age INTEGER, maxLength INTEGER, startPosition INTEGER) FOR
SELECT * FROM test_plpgsql.emp WHERE age = p_age ORDER BY empno LIMIT maxLength OFFSET startPosition;
record1 RECORD;
BEGIN
OPEN curs1(beforAge, maxLength, startPosition);
LOOP
-- カーソルの位置を変更
MOVE curs1;
IF NOT FOUND THEN
-- 全て移動した場合はループを終了
EXIT;
END IF;
-- 引数で指定した年齢の社員かつ、引数で指定した件数分のみUPDATEが実行される
UPDATE test_plpgsql.emp SET age = afterAge WHERE CURRENT OF curs1;
END LOOP;
CLOSE curs1;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT * FROM test_plpgsql.emp WHERE age = 40;
empno | empname | poscode | age
-------+-----------+---------+-----
4 | 営業課長1 | 1 | 40
5 | 営業課長2 | 1 | 40
6 | 営業課長3 | 1 | 40
7 | 開発課長1 | 1 | 40
8 | 開発課長2 | 1 | 40
9 | 開発課長3 | 1 | 40
10 | 人事課長1 | 1 | 40
11 | 人事課長2 | 1 | 40
12 | 人事課長3 | 1 | 40
(9 rows)
example-db=> CALL test_plpgsql.sample21_04_02(40, 48, 3, 1);
CALL
example-db=> SELECT * FROM test_plpgsql.emp WHERE age IN (40,48);
empno | empname | poscode | age
-------+-----------+---------+-----
5 | 営業課長2 | 1 | 40
6 | 営業課長3 | 1 | 40
7 | 開発課長1 | 1 | 40
8 | 開発課長2 | 1 | 40
9 | 開発課長3 | 1 | 40
10 | 人事課長1 | 1 | 40
11 | 人事課長2 | 1 | 48
12 | 人事課長3 | 1 | 48
4 | 営業課長1 | 1 | 48
カーソルクローズについて
CLOSE カーソル名
CLOSE カーソル名
の形式で記載することで、カーソルを閉じることが可能です。
１度開いたカーソルに関して、カーソルを閉じる前に再度開くことはできませんが、
カーソルを閉じた後に再度開くことは可能です。
注意
処理内で例外が発生した場合、カーソルは暗黙的にクローズされます。
サンプルコード
パターン1:CLOSE カーソル名で明示的にクローズする場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_05() AS $$
DECLARE
curs1 refcursor;
record1 RECORD;
BEGIN
RAISE INFO '--curs1 OPEN!------------------------------';
OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
LOOP
FETCH curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
-- CLOSE前にOPENした場合、以下のエラーが発生する
-- SQLSTATE:42P03 SQLERRM:cursor "<unnamed portal 65>" already in use
-- OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
CLOSE curs1;
-- OPENしていない状態でCLOSEした場合、以下のエラーが発生する
-- SQLSTATE:34000 SQLERRM:cursor "<unnamed portal 66>" does not exist
-- CLOSE curs1;
RAISE INFO '--curs1 CLOSE!------------------------------';
RAISE INFO '--curs1 再OPEN!------------------------------';
OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
LOOP
FETCH curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
CLOSE curs1;
RAISE INFO '--curs1 再CLOSE!------------------------------';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_05();
INFO: --curs1 OPEN!------------------------------
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
INFO: --curs1 CLOSE!------------------------------
INFO: --curs1 再OPEN!------------------------------
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
INFO: --curs1 再CLOSE!------------------------------
CALL
パターン2：例外が発生し、カーソルが暗黙的にクローズする場合
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_06() AS $$
DECLARE
curs1 refcursor;
record1 RECORD;
BEGIN
OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
LOOP
FETCH curs1 INTO record1;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
-- EXCEPTIONに飛ばす
RAISE EXCEPTION NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS THEN
-- EXCEPTIONに到達した時点でカーソルが自動クローズされるため、エラーとなる
CLOSE curs1;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_06();
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
ERROR: cursor "<unnamed portal 7>" does not exist
CONTEXT: PL/pgSQL function test_plpgsql.sample21_06() line 23 at CLOSE
カーソル結果に対するループについて
FOR RECORD型変数名 IN カーソル名 LOOP 処理内容 END LOOP
FOR RECORD型変数名 IN カーソル名 LOOP 処理内容 END LOOP;
の形式で記載することで、FOR文内で自動的にカーソルを開き、繰り返し処理が終了した段階で
カーソルを自動的に閉じることが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample21_07(char(5)) AS $$
DECLARE
curs1 CURSOR FOR
SELECT * FROM test_plpgsql.dept;
curs2 CURSOR (target char(5)) FOR
SELECT * FROM test_plpgsql.dept WHERE deptno = target;
record1 test_plpgsql.dept%ROWTYPE;
record2 test_plpgsql.dept%ROWTYPE;
BEGIN
RAISE INFO '--curs1 OPEN!------------------------------';
FOR record1 IN curs1 LOOP
RAISE INFO 'deptno:% deptname:%', record1.deptno, record1.deptname;
END LOOP;
RAISE INFO '--curs1 CLOSE!------------------------------';
RAISE INFO '--curs2 OPEN!------------------------------';
FOR record2 IN curs2($1) LOOP
RAISE INFO 'deptno:% deptname:%', record2.deptno, record2.deptname;
END LOOP;
RAISE INFO '--curs2 CLOSE!------------------------------';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample21_07('2');
INFO: --curs1 OPEN!------------------------------
INFO: deptno:0 deptname:営業部
INFO: deptno:1 deptname:開発部
INFO: deptno:2 deptname:人事部
INFO: --curs1 CLOSE!------------------------------
INFO: --curs2 OPEN!------------------------------
INFO: deptno:2 deptname:人事部
INFO: --curs2 CLOSE!------------------------------
エラーとメッセージ
RAISEについて
RAISE文を使用することで、メッセージの出力や、例外の発生が可能となります。
記載の仕方はさまざまありますが、一例を挙げると以下です。
RAISE ログレベル '文字列'
RAISE ログレベル 例外名
RAISE ログレベル SQLSTATE 'SQLSTARE' USING MESSAGE = '文字列'
RAISEで設定するログレベルについて
ログレベルの箇所に関しては、
DEBUG、LOG、INFO、NOTICE、WARNINGおよびEXCEPTIONを指定することができ、指定がない場合はEXCEPTION扱いとなります。
EXCEPTIONの場合はエラーが発生します。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_01() AS $$
DECLARE
BEGIN
RAISE DEBUG 'メッセージレベル：DEBUG';
RAISE LOG 'メッセージレベル：LOG';
RAISE INFO 'メッセージレベル：INFO';
RAISE NOTICE 'メッセージレベル：NOTICE';
RAISE WARNING 'メッセージレベル：WARNING';
RAISE EXCEPTION 'メッセージレベル：EXCEPTION';
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample22_01();
INFO: メッセージレベル：INFO
NOTICE: メッセージレベル：NOTICE
WARNING: メッセージレベル：WARNING
ERROR: SQLSTATE:P0001 SQLERRM:メッセージレベル：EXCEPTION
CONTEXT: PL/pgSQL function test_plpgsql.sample22_01() line 14 at RAISE
example-db=> set client_min_messages = 'debug';
SET
example-db=> CALL test_plpgsql.sample22_01();
DEBUG: メッセージレベル：DEBUG
LOG: メッセージレベル：LOG
INFO: メッセージレベル：INFO
NOTICE: メッセージレベル：NOTICE
WARNING: メッセージレベル：WARNING
ERROR: SQLSTATE:P0001 SQLERRM:メッセージレベル：EXCEPTION
CONTEXT: PL/pgSQL function test_plpgsql.sample22_01() line 14 at RAISE
RAISEで設定する文字列について
RAISE ログレベル '文字列'の形式で文字列を出力できますが、
動的に文字列を出力させたい場合は、%を利用します。
例
RAISE EXCEPTION 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
出力内容
SQLSTATE:55000 SQLERRM:cursor can only scan forward
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_02() AS $$
DECLARE
target NUMERIC;
BEGIN
-- 1から10までの値がランダムに生成される
SELECT ROUND(( RANDOM() * (1 - 10) )::NUMERIC, 0) + 10 INTO target;
RAISE INFO '出力値：%', target;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample22_02();
INFO: 出力値：9
CALL
RAISEで設定する例外名 / SQLSTATEについて
RAISE 例外名
RAISE SQLSTATE 'SQLSTATE'
の形式で記載することで、例外名、SQLSTATEを明示的に発生させることが可能です。
また、発生させたエラーをEXCEPTION内でキャッチすることも可能です。
例）
RAISE division_by_zero;
RAISE SQLSTATE '22012';
PostgreSQLで利用するSQLSTATEおよび例外名の一覧は以下のサイトに記載があります。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_03() AS $$
DECLARE
BEGIN
RAISE INFO NO_DATA_FOUND;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE INFO 'NO_DATA_FOUNDです';
WHEN OTHERS THEN
RAISE INFO 'それ以外のエラーです';
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample22_03();
INFO: no_data_found
CALL
RAISEで設定する詳細情報について
RAISE USING ...のUSING以降に、エラー発生時の詳細情報を設定可能です。
設定できる項目は以下です。
| 名前 | 説明 |
|---|---|
| MESSAGE | エラーメッセージテキストを設定します。 |
| DETAIL | エラー詳細メッセージを設定します。 |
| HINT | ヒントメッセージを設定します。 |
| ERRCODE | 例外名またはSQLSTATEを設定します。 |
| COLUMN | COLUMN名を指定します。 |
| CONSTRAINT | CCONSTRAINT名を指定します。 |
| DATATYPE | DATATYPE名を指定します。 |
| TABLE | TABLE名を指定します。 |
| SCHEMA | CHEMA名を指定します。 |
また、GET STACKED DIAGNOSTICSを用いて、呼び出し元のECXEPTION内で上記で設定した詳細情報を取得することが可能です。
サンプルコード
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_04(INTEGER) AS $$
DECLARE
myrow record;
BEGIN
CASE $1
WHEN 1 THEN
-- 一意制約違反を発生させる
INSERT INTO test_plpgsql.dept VALUES ('0','営業部');
WHEN 2 THEN
-- NO_DATA_FOUNDを発生させる
SELECT * INTO STRICT myrow FROM test_plpgsql.emp WHERE empno = '0';
END CASE;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
RAISE EXCEPTION SQLSTATE '99999' USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || sqlerrm,
HINT='[HINT]ヒントメッセージを出力します' ,
DETAIL='[DETAIL]エラー詳細メッセージを出力します';
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'SQLATATE:%, SQLERRM:%', SQLSTATE, SQLERRM USING HINT='[HINT]ヒントメッセージを出力します' ,
DETAIL='[DETAIL]エラー詳細メッセージを出力します',
ERRCODE='99999';
WHEN OTHERS THEN
RAISE EXCEPTION SQLSTATE '99999' USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample22_04(1);
ERROR: SQLATATE:23505, SQLERRM:duplicate key value violates unique constraint "dept_pkey"
DETAIL: [DETAIL]エラー詳細メッセージを出力します
HINT: [HINT]ヒントメッセージを出力します
CONTEXT: PL/pgSQL function test_plpgsql.sample22_04(integer) line 17 at RAISE
example-db=> CALL test_plpgsql.sample22_04(2);
ERROR: SQLATATE:P0002, SQLERRM:query returned no rows
DETAIL: [DETAIL]エラー詳細メッセージを出力します
HINT: [HINT]ヒントメッセージを出力します
CONTEXT: PL/pgSQL function test_plpgsql.sample22_04(integer) line 21 at RAISE
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_05_01() AS $$
DECLARE
myrow record;
BEGIN
-- 一意制約違反を発生させる
INSERT INTO test_plpgsql.dept VALUES ('0','営業部');
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
RAISE EXCEPTION SQLSTATE '99999' USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || sqlerrm,
HINT='[HINT]ヒントメッセージを出力します' ,
DETAIL='[DETAIL]エラー詳細メッセージを出力します',
COLUMN='deptno',
CONSTRAINT='dept_pkey',
DATATYPE='char(5)',
TABLE='dept',
SCHEMA='test_plpgsql';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE PROCEDURE test_plpgsql.sample22_05() AS $$
DECLARE
text_var1 TEXT;
text_var2 TEXT;
text_var3 TEXT;
text_var4 TEXT;
text_var5 TEXT;
text_var6 TEXT;
text_var7 TEXT;
text_var8 TEXT;
text_var9 TEXT;
text_var10 TEXT;
BEGIN
CALL test_plpgsql.sample27_05_01();
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = RETURNED_SQLSTATE,
text_var2 = COLUMN_NAME,
text_var3 = CONSTRAINT_NAME,
text_var4 = PG_DATATYPE_NAME,
text_var5 = MESSAGE_TEXT,
text_var6 = TABLE_NAME,
text_var7 = SCHEMA_NAME,
text_var8 = PG_EXCEPTION_DETAIL,
text_var9 = PG_EXCEPTION_HINT,
text_var10 = PG_EXCEPTION_CONTEXT;
RAISE INFO 'RETURNED_SQLSTATE:%',text_var1;
RAISE INFO 'COLUMN_NAME:%',text_var2;
RAISE INFO 'CONSTRAINT_NAME:%',text_var3;
RAISE INFO 'PG_DATATYPE_NAME:%',text_var4;
RAISE INFO 'MESSAGE_TEXT:%',text_var5;
RAISE INFO 'TABLE_NAME:%',text_var6;
RAISE INFO 'SCHEMA_NAME:%',text_var7;
RAISE INFO 'PG_EXCEPTION_DETAIL:%',text_var8;
RAISE INFO 'PG_EXCEPTION_HINT:%',text_var9;
RAISE INFO 'PG_EXCEPTION_CONTEXT:%',text_var10;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample22_05();
INFO: RETURNED_SQLSTATE:42883
INFO: COLUMN_NAME:
INFO: CONSTRAINT_NAME:
INFO: PG_DATATYPE_NAME:
INFO: MESSAGE_TEXT:procedure test_plpgsql.sample27_05_01() does not exist
INFO: TABLE_NAME:
INFO: SCHEMA_NAME:
INFO: PG_EXCEPTION_DETAIL:
INFO: PG_EXCEPTION_HINT:No procedure matches the given name and argument types. You might need to add explicit type casts.
INFO: PG_EXCEPTION_CONTEXT:PL/pgSQL function test_plpgsql.sample22_05() line 15 at CALL
CALL
トランザクション制御
トランザクション制御について
PL/pgSQLでは、プロシージャに関して、COMMITおよびROLLBACKコマンドを用いてトランザクションの制御をすることが可能です。
また、BEGIN~EXCEPTIONのブロック内で例外が発生した場合、
BEGINブロック内の処理は自動的にロールバックされます。
サンプルコード
パターン1：偶数の場合は明示的にCOMMIT、奇数の場合は明示的にROLLBACK
CREATE OR REPLACE PROCEDURE test_plpgsql.sample23_01() AS $$
DECLARE
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test_plpgsql.test (col1, col2) VALUES (i, i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample23_01();
CALL
example-db=> SELECT * FROM test_plpgsql.test;
col1 | col2
------+------
0 | 0
2 | 2
4 | 4
6 | 6
8 | 8
(5 rows)
パターン2：BEGIN~EXCEPTIONのブロック内で例外が発生
CREATE OR REPLACE PROCEDURE test_plpgsql.sample23_02() AS $$
DECLARE
BEGIN
INSERT INTO test_plpgsql.test (col1, col2) VALUES (1, '1');
BEGIN
INSERT INTO test_plpgsql.test (col1, col2) VALUES (2, '2');
-- 一意制約違反発生
INSERT INTO test_plpgsql.test (col1, col2) VALUES (2, '2');
EXCEPTION
WHEN OTHERS THEN
RAISE INFO 'SQLATATE:%, SQLERRM:%', SQLSTATE, SQLERRM;
RAISE INFO '自動ロールバック発生！！';
END;
INSERT INTO test_plpgsql.test (col1, col2) VALUES (3, '3');
COMMIT;
INSERT INTO test_plpgsql.test (col1, col2) VALUES (4, '4');
ROLLBACK;
INSERT INTO test_plpgsql.test (col1, col2) VALUES (5, '5');
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> CALL test_plpgsql.sample23_02();
INFO: SQLATATE:23505, SQLERRM:duplicate key value violates unique constraint "test_pkey"
INFO: 自動ロールバック発生！！
CALL
example-db=> SELECT * FROM test_plpgsql.test;
col1 | col2
------+------
1 | 1
3 | 3
5 | 5
(3 rows)
対応不可のパターンについて
PL/pgSQLにおいて、上記の通り、COMMIT/ROLLBACKの機能は存在しますが、
以下の通り、PL/SQLでできるようなことがPL/pgSQLではできないというパターンも存在します。
- SAVEPOINTは利用できない
- FUNCTION内でCOMMIT/ROLLBACKが出来ない
- FUNCTION内でCOMMIT/ROLLBACKを行うPROCEDUREを呼び出した場合でもCOMMIT/ROLLBACKが出来ない
- 明示的なトランザクション内でのCOMMIT/ROLLBACKを行うPROCEDUREを呼び出した場合でもCOMMIT/ROLLBACKが出来ない
-
BEGIN~EXCEPTION内でCOMMIT/ROLLBACKが出来ない
詳細は別記事（PL/SQLとPL/pgSQLのトランザクション制御の違いに関する検証）にまとめたので、興味のある方は確認してみてください
サンプルコード
NG例：FUNCTION内でCOMMIT/ROLLBACKが出来ない
CREATE OR REPLACE FUNCTION test_plpgsql.sample23_03() RETURNS VOID AS $$
DECLARE
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test_plpgsql.test (col1, col2) VALUES (i, i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
実行結果
example-db=> SELECT test_plpgsql.sample23_03();
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function test_plpgsql.sample23_03() line 7 at COMMIT
参考