【PL/pgSQL入門】PL/pgSQLを勉強したのでまとめてみた

Last updated at 2024-04-09Posted at 2022-01-17

はじめに

PL/pgSQLに関して、個人的に作成したサンプルコードをまとめた記事となります。

基本的にPL/pgSQLの仕様については公式ドキュメントを見れば理解できると思いますが、
サンプルコードが少なく、どう実装すればいいんだっけ？となる場面も多々あったので、
PL/pgSQLを実装する際に、この記事がお役に立てば幸いです。

PostgreSQLのバージョンについて

本記事におけるpostgreSQLのバージョンは12.4で扱っていきます。

本記事で公開しているサンプルコードについて

本記事で公開しているサンプルコードは、GitHubにもまとめてアップロードしております。

事前準備(テストデータ投入)

今回のサンプルコードを作成するにあたり、検証用の環境を用意しました。
検証する際に是非活用してください。
なお、テストデータ投入そのものにPL/pgSQLを利用しています。

テストデータ作成SQL

setup.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！！'; -- コンソールに「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！！'; -- コンソールに「HELLO WORLD！！」が出力
END;	
$$ LANGUAGE plpgsql;

プロシージャの作成

CREATE OR REPLACE PROCEDURE 関数名(引数)
で作成することができます。

プロシージャの実行

CALL プロシージャ名(引数);で実行することが可能です

実行結果

example-db=> CALL test_plpgsql.sample1_02();
INFO:  HELLO WORLD！！
CALL

ファンクションとプロシージャの違い

ファンクションとプロシージャの主な違いは以下が挙げられます。

	実行方法	戻り値の有無	トランザクション制御（COMMIT／ROLLBACKコマンド等）
ファンクション	SQLで実行	有	不可
プロシージャ	CALL文	無	可能（一部制限有り）

関数本文について

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以降に関数を定義する文字列定数を記載します。
シングルクォーテーション（'）で囲うことも可能ですが、関数内でシングルクォーテーション利用する場合、エスケープする必要があります。

シングルクォーテーション（'）で囲う場合

/* *******************************************
 * 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ブロックは、副ブロックとして入れ子の形式で囲うことが可能です。

副ブロック内で外側のブロックと同名の変数を宣言可能ですが、副ブロック内で外側のブロックを参照する際は
外側のブロックの先頭に<< ラベル名 >>を記載して、ラベル名.変数名で参照することが可能です。

/* *******************************************
 * 副ブロックに関するサンプルコード
 * 参考リンク：
 * https://www.postgresql.jp/document/12/html/test_plpgsql-structure.html
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample2_01(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の代入は不可となります。

サンプルコード

/* *******************************************
 * 変数 / 定数 / デフォルト値 / NOT NULLについてのサンプルコード
 * 参考リンク：
 * https://www.postgresql.jp/document/12/html/test_plpgsql-declarations.html
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_01() AS $$
DECLARE	
-- 変数
url VARCHAR := 'https://www.postgresql.jp/document/';
-- 定数
user_id CONSTANT INTEGER := 10;
-- NOT NULL / デフォルト値
tax NUMERIC(3,2) NOT NULL DEFAULT 1.08;
/* 【NG例】
 * NOT NULLの場合に初期値を設定しない場合はエラーとなる
 * tax NUMERIC(3,2) NOT NULL;
 * [出力結果]: ERROR: variable "tax" must have a default value, since it's declared NOT NULL
 */

BEGIN
    RAISE INFO '** 変数の確認 ********************************';
	RAISE INFO '[変更前]urlの値：%',url;
	url = 'https://www.postgresql.jp/document/13/html/';
	RAISE INFO '[変更後]urlの値：%',url;

    /* 【NG例】
     * 定数に代入した場合はエラーとなる
     * user_id := 11;
     * [出力結果]:variable "user_id" is declared CONSTANT
     */
	RAISE INFO '** 定数の確認 ********************************';
	RAISE INFO 'user_idの値：%',user_id;
    
	RAISE INFO '** NOT NULL / デフォルト値の確認 **************';
    RAISE INFO '[変更前]taxの値：%',tax;
	/* 【NG例】
     * 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:  ** 変数の確認 ********************************
INFO:  [変更前]urlの値：https://www.postgresql.jp/document/
INFO:  [変更後]urlの値：https://www.postgresql.jp/document/13/html/
INFO:  ** 定数の確認 ********************************
INFO:  user_idの値：10
INFO:  ** NOT NULL / デフォルト値の確認 **************
INFO:  [変更前]taxの値：1.08
INFO:  [変更後]taxの値：1.10
CALL

%TYPE / %ROWTYPEについて

%TYPE

変数名テーブル名.カラム名%TYPE
の形式で記載することで、関数作成時にテーブルのデータ型を参照して該当のカラムと同一の型が定義されます。

%ROWTYPE

変数名テーブル名%ROWTYPE
の形式で記載することで、関数作成時に指定したテーブルの列定義を持つ複合型が定義されます。
複合型は、SELECTの問い合わせの結果を保持する際などで利用されます。
また、変数名.列名で参照することが可能です。

サンプルコード　

/* *******************************************
 * %ROWTYPE / %TYPEについてのサンプルコード
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_02() AS $$
DECLARE	
-- 型のコピー　%TYPE
-- deptテーブルのdeptnoカラムはcharacter型のため、character型となる
myfield test_plpgsql.dept.deptno%TYPE;
-- 行型　%ROWTYPE
myrow test_plpgsql.member%ROWTYPE; 

BEGIN	
	-- 型のコピー　%TYPE
	RAISE INFO '** TYPEの確認 *******************************';
	SELECT deptno INTO myfield FROM test_plpgsql.dept ORDER BY deptno LIMIT 1;
	RAISE INFO 'myfieldの値:%, myfieldの型:%', myfield, pg_typeof(myfield);

	-- 行型　%ROWTYPE
	RAISE INFO '** 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:  ** TYPEの確認 *******************************
INFO:  myfieldの値:0    , myfieldの型:character
INFO:  ** ROWTYPEの確認 ****************************
INFO:  myrow.deptnoの値:0    , myrow.empnoの値:1    
CALL

RECORD型（複合型）について

RECORD型

変数名 RECORD
の形式で記載します。

%ROWTYPEと似た複合型ですが、%ROWTYPEと異なる点として、宣言時に具体的な列のデータは保持しておらず、SELECTなどで値が代入されたタイミングで実際の列定義が決まります。
RECODE型変数に格納された列のデータに関しては、変数名.列名で参照することが可能です。

サンプルコード

/* *******************************************
 * RECORD型についてのサンプルコード
 * 問い合わせ結果による繰り返しで利用
 * [FOR target IN query LOOP...]
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample3_03() AS $$
DECLARE	
arow RECORD;

BEGIN	
	FOR arow IN SELECT * FROM test_plpgsql.member ORDER BY deptno LOOP
		RAISE INFO 'myrow.deptnoの値:%, myrow.empnoの値:%', myrow.deptno, myrow.empno;
	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;
    
	/* 【NG例】
     * 入れ子のユーザ定義を設定する際、以下のやり方だとエラーになる
     * 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;
	
	/* 【NG例】
     * 入れ子のユーザ定義の参照の仕方に注意
     * 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[];
	/* 【NG例】
     * %TYPEを用いて配列を作成することは不可
     * arr2 test_plpgsql.dept.deptno%type[];
     * [出力結果]:NG 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;

	/* 【NG例】
     * 配列[添字].ユーザ定義の項目名　といった記載の仕方はエラーとなる
     * arrDataType[2].param1 := 3;
     * [出力結果]:[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 '[別名]第一引数の値：%', initnum;
	RAISE INFO '[別名]第二引数の値：%', addnum;
	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を使用します。

サンプルコード

/* *******************************************
 * PERFORMについてのサンプルコード（結果を伴わないコマンドの実行）
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample6_01() AS $$
DECLARE	
	myrow record;
	message text;
BEGIN
	/* 【NG例】
     * SELECT文を実行するとエラーとなる
     * 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 または PERFORM を利用することでエラーを回避することができる
	SELECT * INTO myrow FROM pg_tables ORDER BY schemaname;
	RAISE INFO 'myrowからスキーマ名とテーブル名を出力：%', myrow.schemaname || '.' || myrow.tablename;
	PERFORM * from test_plpgsql.member;
	/* 【NG例】
     * FUNCTIONをSELECT FUNCTION名;で実行するとエラーとなる
     * 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 または PERFORM を利用することでエラーを回避することができる
    SELECT * into message FROM test_plpgsql.sample6_01_01();
    RAISE INFO '%', message;
	PERFORM test_plpgsql.sample5_02_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：レコードを１件取得する場合

/* *******************************************
 * 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件取得する場合

/* *******************************************
 * 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件以上取得する場合

/* *******************************************
 * 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`無し / 更新件数２件

/* *******************************************
 * INSERT / UPDATE / DELETE RETURNING（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件

/* *******************************************
 * INSERT / UPDATE / DELETE RETURNING（STRICT有り）についてのサンプルコード
 * *******************************************/
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コマンドを実行したい場合に利用します。

サンプルコード

/* *******************************************
 * EXECUTEについてのサンプルコード
 * *******************************************/
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に指定するコマンドのパラメータを渡したい場合は、「EXECUTE USING」を利用する
	EXECUTE 'INSERT INTO sample11 (col1, col2) VALUES ($1, $2);' USING $1, $2;

	-- EXECUTEに指定するコマンドの実行結果を受け取りたい場合は、「EXECUTE INTO」を利用する
	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`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（SELECT INTOの場合）
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_01() AS $$
DECLARE	
	myrow record;
BEGIN
    RAISE INFO '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;
	
	RAISE INFO '** SELECT INTO実行後の確認（レコード有り） ********';
	SELECT * INTO myrow FROM test_plpgsql.emp WHERE poscode = '1';
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF FOUND THEN
		RAISE INFO 'レコードが存在します！';
	END IF;

    RAISE INFO '** SELECT INTO実行後の確認（レコード無し） ********';
	SELECT * INTO myrow FROM test_plpgsql.emp WHERE empno = '0';
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF NOT FOUND THEN
		RAISE INFO 'レコードが存在しません！';
	END IF;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> CALL test_plpgsql.sample11_01();
INFO:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** SELECT INTO実行後の確認（レコード有り） ********
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  ** SELECT INTO実行後の確認（レコード無し） ********
INFO:  FOUNDの値:f
INFO:  レコードが存在しません！
CALL

`PERFORM`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（PERFORMの場合）
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_02() AS $$
DECLARE	
BEGIN
    RAISE INFO '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;

	RAISE INFO '** PERFORM実行後の確認（レコード有り） ************';
	PERFORM * FROM test_plpgsql.emp WHERE poscode = '1';
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF FOUND THEN
		RAISE INFO 'レコードが存在します！';
	END IF;

	RAISE INFO '** PERFORM実行後の確認（レコード無し） ************';
	PERFORM * FROM test_plpgsql.emp WHERE empno = '0';
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF NOT FOUND THEN
		RAISE INFO 'レコードが存在しません！';
	END IF;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> CALL test_plpgsql.sample11_02();
INFO:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** PERFORM実行後の確認（レコード有り） ************
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  ** PERFORM実行後の確認（レコード無し） ************
INFO:  FOUNDの値:f
INFO:  レコードが存在しません！
CALL

`UPDATE`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（UPDATEの場合）
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.sample11_03() AS $$
DECLARE	
BEGIN
    RAISE INFO '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;

	RAISE INFO '** UPDATE実行後の確認（レコード有り） ************';
	UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1002';	
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF FOUND THEN
		RAISE INFO 'レコードが存在します！';
	END IF;

	RAISE INFO '** UPDATE実行後の確認（レコード無し） ************';
	UPDATE test_plpgsql.emp SET empname = '人事社員30001' WHERE empno = '1003';
	RAISE INFO 'FOUNDの値:%', FOUND;
	IF NOT FOUND THEN
		RAISE INFO 'レコードが存在しません！';
	END IF;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> CALL test_plpgsql.sample11_03();
INFO:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** UPDATE実行後の確認（レコード有り） ************
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  ** UPDATE実行後の確認（レコード無し） ************
INFO:  FOUNDの値:f
INFO:  レコードが存在しません！
CALL

`FETCH`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（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 '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;

    RAISE INFO '** FETCH実行後の確認 **************';
	OPEN empnames($1);	
	LOOP
	FETCH empnames INTO empname;
	RAISE INFO 'FOUNDの値:%', 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:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** FETCH実行後の確認 **************
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:t
INFO:  レコードが存在します！
INFO:  FOUNDの値:f
INFO:  レコードが存在しません！
CALL

`MOVE`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（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 '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;

	OPEN poanames($1);
	RAISE INFO '** MOVE実行後の確認（MOVE成功） ************';
	MOVE FORWARD 4 IN poanames;	
	RAISE INFO 'FOUNDの値:%', FOUND;

	RAISE INFO '** MOVE実行後の確認（MOVE失敗） ************';
	MOVE FORWARD 4 IN poanames;	
	RAISE INFO 'FOUNDの値:%', FOUND;
	FETCH poanames INTO poaname;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> CALL test_plpgsql.sample11_05('1');
INFO:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** MOVE実行後の確認（MOVE成功） ************
INFO:  FOUNDの値:t
INFO:  ** MOVE実行後の確認（MOVE失敗） ************
INFO:  FOUNDの値:f
CALL

`RETURN QUERY`の場合

/* *******************************************
 * FOUNDについてのサンプルコード（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 '** 初期値の確認 ********************************';
	RAISE INFO 'FOUNDの値:%', FOUND;

	RAISE INFO '** RETURN QUERY実行後の確認 ********************';
	RETURN QUERY SELECT * FROM test_plpgsql.pos WHERE poscode = $1;	
	RAISE INFO 'FOUNDの値:%', FOUND;
	RETURN;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> SELECT test_plpgsql.sample11_06('1');
INFO:  ** 初期値の確認 ********************************
INFO:  FOUNDの値:f
INFO:  ** RETURN QUERY実行後の確認 ********************
INFO:  FOUNDの値:t
 sample11_06 
-------------
 (1,課長)
(1 row)

制御構造

IN / INOUT / OUTパラメータモードについて

関数の引数にはIN、OUT、INOUTという３つのパラメータモードを指定することができます。

例）
FUNCTION名(変数名 IN 型)
FUNCTION名(変数名 IN OUT 型)
FUNCTION名(変数名 OUT 型)

INパラメータ

呼び出し元から値を受けとるパラメータです。
パラメータモードの指定がない場合のデフォルトは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）

/* *******************************************
 * OUTパラメータについてのサンプルコード(OUTパラメータが1つ)
 * *******************************************/
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;
	/* 【NG例】
     * RETURNの際にOUTパラメータを指定するとエラーとなる
     * RETURN empAge;
     * [出力結果]: RETURN cannot have a parameter in function with OUT parameters
     */
    RETURN;
END;	
$$ LANGUAGE plpgsql;

実行結果

example-db=> SELECT test_plpgsql.sample12_02('営業部長');
INFO:  営業部長さんの年齢は45歳です
 sample12_02 
-------------
          45
(1 row)

OUTパラメータが2つ以上の場合

戻り値の型がRECODE型となります。（戻り値を指定しなくてもOK）

/* *******************************************
 * OUTパラメータについてのサンプルコード(OUTパラメータが1つ)
 * *******************************************/
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パラメータ両方の役割を持ったパラメータモードです。
呼び出し元から値を受けとり、最終的に呼び出し元へ値を返却します。

サンプルコード

/* *******************************************
 * INOUTパラメータについてのサンプルコード
 * *******************************************/
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

関数の処理を終了させる、呼び出し元に値を返却させるために利用します。

サンプルコード

戻り値宣言が存在する場合

/* *******************************************
 * 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パラメータが存在する場合

/* *******************************************
 * RETURNについてのサンプルコード(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;
    /* 【NG例】
     * RETURNの際にOUTパラメータを指定するとエラーとなる
     * RETURN empAge;
     * [出力結果]: RETURN cannot have a parameter in function with OUT parameters
     */
	RETURN;
END;    
$$ LANGUAGE plpgsql;

実行結果

example-db=> SELECT test_plpgsql.sample13_02('営業部長');
INFO:  営業部長さんの年齢は45歳です
 sample13_02 
-------------
          45
(1 row)

戻り値宣言　／　OUTパラメータが存在しない場合

/* *******************************************
 * RETURNについてのサンプルコード(戻り値宣言／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`の場合

/* *******************************************
 * 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;
END;    
$$ LANGUAGE plpgsql;

実行結果

example-db=> SELECT test_plpgsql.sample14_01();
 sample14_01 
-------------
 (0,部長)
 (1,課長)
 (2,主任)
 (3,社員)
(4 rows)

`TABLE(columns)`の場合

/* *******************************************
 * RETURN NEXTについてのサンプルコード(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が実行されたタイミングで関数の処理が終了する、呼び出し元に値を返却するわけではありません。

サンプルコード

/* *******************************************
 * 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文
と記載することで、その列を戻り値として返すことができます。

サンプルコード

/* *******************************************
 * RETURN QUERY EXECUTEについてのサンプルコード
 * *******************************************/
CREATE OR REPLACE FUNCTION test_plpgsql.sample15_02(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

実行結果（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

カーソル

カーソルについて

カーソルはPL/pgSQL上でSELECT文を呼びだす際に、実行結果を１行ずつ返却して、プログラム上で処理するために利用します。

カーソルは基本的に、以下の流れで利用されます。
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`で宣言した場合

/* *******************************************
 * カーソル変数の宣言についてのサンプルコード(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文`で宣言した場合

/* *******************************************
 * カーソル変数の宣言についてのサンプルコード(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 変数名
の形式で記載することにより、データを取り出さないでカーソルの位置を変更することが可能です。

サンプルコード

/* *******************************************
 * カーソルの利用についてのサンプルコード(SCROLL／MOVE)
 * *******************************************/
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

/* *******************************************
 * カーソルの利用についてのサンプルコード(FETCH)
 * *******************************************/
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 カーソル名
の形式で記載することで、カーソルによって特定した行を更新または消去することが可能です。

サンプルコード

/* *******************************************
 * カーソルの利用についてのサンプルコード(UPDATE / DELETE 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;

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 カーソル名`で明示的にクローズする場合

/* *******************************************
 * カーソルクローズについてのサンプルコード(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;
    /* 【NG例】
     * CLOSE前にOPENした場合、以下のエラーが発生する
     * OPEN curs1 FOR SELECT * FROM test_plpgsql.dept ORDER BY deptno;
     * [出力結果]: SQLSTATE:42P03 SQLERRM:cursor "<unnamed portal 65>" already in use
     */

    CLOSE curs1;
    /* 【NG例】
     * OPENしていない状態でCLOSEした場合、以下のエラーが発生する
     * CLOSE curs1;
     * [出力結果]: SQLSTATE:34000 SQLERRM:cursor "<unnamed portal 66>" does not exist
     */
    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の場合はエラーが発生します。

コンソールに出力されるメッセージレベルの設定ついて

コンソールに出力するメッセージレベルの設定は以下のコマンドで実行します。

メッセージレベル設定例

set client_min_messages = 'メッセージレベル';

注） メッセージレベルの箇所はDEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、LOG、NOTICE、WARNINGのいずれかを指定

なお、INFOレベルのメッセージに関しては、メッセージレベルの設定によらず常に出力されます。

サンプルコード

/* *******************************************
 * RAISEについてのサンプルコード
 * *******************************************/
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;

実行結果(メッセージレベルがwarningの場合)

example-db=> show client_min_messages;
 client_min_messages 
---------------------
 warning
(1 row)

example-db=> CALL test_plpgsql.sample22_01();
INFO:  メッセージレベル：INFO
WARNING:  メッセージレベル：WARNING
ERROR:  SQLSTATE:P0001 SQLERRM:メッセージレベル：EXCEPTION
CONTEXT:  PL/pgSQL function test_plpgsql.sample22_01() line 14 at RAISE

実行結果(メッセージレベルがdebug2の場合)

example-db=> set client_min_messages = 'debug';
SET
example-db=> show client_min_messages;
 client_min_messages 
---------------------
 debug2
(1 row)

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

サンプルコード

/* *******************************************
 * RAISEで設定する文字列についてのサンプルコード
 * *******************************************/
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および例外名の一覧は以下のサイトに記載があります。

サンプルコード

/* *******************************************
 * RAISEで設定する例外名 / 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内で上記で設定した詳細情報を取得することが可能です。

サンプルコード

/* *******************************************
 * RAISEで設定する詳細情報についてのサンプルコード
 * *******************************************/
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

/* *******************************************
 * 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.sample22_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:99999
INFO:  COLUMN_NAME:deptno
INFO:  CONSTRAINT_NAME:dept_pkey
INFO:  PG_DATATYPE_NAME:char(5)
INFO:  MESSAGE_TEXT:SQLATATE:23505, SQLERRM:duplicate key value violates unique constraint "dept_pkey"
INFO:  TABLE_NAME:dept
INFO:  SCHEMA_NAME:test_plpgsql
INFO:  PG_EXCEPTION_DETAIL:[DETAIL]エラー詳細メッセージを出力します
INFO:  PG_EXCEPTION_HINT:[HINT]ヒントメッセージを出力します
INFO:  PG_EXCEPTION_CONTEXT:PL/pgSQL function test_plpgsql.sample22_05_01() line 11 at RAISE
SQL statement "CALL test_plpgsql.sample22_05_01()"
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が出来ない

/* *******************************************
 * トランザクション制御についてのサンプルコード(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

無名ブロック

無名ブロックについて

本記事ではこれまで、プロシージャやファンクションを作成することでPL/pgSQLの処理を実行していました。
ただし、それ以外に、無名ブロックの仕組みを利用することで、プロシージャやファンクションを実装せずとも、PL/pgSQLの処理の実行が可能となります。

無名ブロックはDOを用いて実装します。

サンプルコード

/* *******************************************
 * 無名ブロックについてのサンプルコード
 * *******************************************/
DO $$
DECLARE
    dataType test_plpgsql.DATA_TYPE1;
BEGIN
    dataType.param1 := 9;
    dataType.param2 := '無名ブロックを経由して、ユーザー定義型を引数に持つプロシージャを呼び出し';
    dataType.param3 := convert_to('無名ブロックを経由して、ユーザー定義型を引数に持つプロシージャを呼び出し', 'UTF-8');
    CALL test_plpgsql.sample24_01(dataType);
END
$$ LANGUAGE plpgsql; 

CREATE OR REPLACE PROCEDURE test_plpgsql.sample24_01(p_dataType test_plpgsql.DATA_TYPE1 ) AS $$
DECLARE
BEGIN
    RAISE INFO 'DATA_TYPE1.param1:%', p_dataType.param1;
    RAISE INFO 'DATA_TYPE1.param2:%', p_dataType.param2;
    RAISE INFO 'DATA_TYPE1.param3:%', p_dataType.param3;
END;
$$ LANGUAGE plpgsql;

実行結果

example-db=> DO $$
DECLARE
    dataType test_plpgsql.DATA_TYPE1;
BEGIN
    dataType.param1 := 9;
    dataType.param2 := '無名ブロックを経由して、ユーザー定義型を引数に持つプロシージャを呼び出し';
    dataType.param3 := convert_to('無名ブロックを経由して、ユーザー定義型を引数に持つプロシージャを呼び出し', 'UTF-8');
    CALL test_plpgsql.sample24_01(dataType);
END
$$ LANGUAGE plpgsql; 
INFO:  DATA_TYPE1.param1:9
INFO:  DATA_TYPE1.param2:無名ブロックを経由して、ユーザー定義型を引数に持つプロシージャを呼び出し
INFO:  DATA_TYPE1.param3:\xe784a1e5908de38396e383ade38383e382afe38292e7b58ce794b1e38197e381a6e38081e383a6e383bce382b6e383bce5ae9ae7bea9e59e8be38292e5bc95e695b0e381abe68c81e381a4e38397e383ade382b7e383bce382b8e383a3e38292e591bce381b3e587bae38197
DO

参考

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

【PL/pgSQL入門】PL/pgSQLを勉強したのでまとめてみた

はじめに

PostgreSQLのバージョンについて

本記事で公開しているサンプルコードについて

目次

事前準備(テストデータ投入)

PL/pgSQLの構造

ファンクションについて

ファンクションの作成

ファンクションの実行

ファンクションの削除

プロシージャについて

プロシージャの作成

プロシージャの実行

ファンクションとプロシージャの違い

関数本文について

DECLARE

BEGIN

EXCEPTION

END;

AS $$ 〜 $$

LANGUAGE plpgsql;

副ブロックについて

宣言

変数 / 定数 / デフォルト値について

変数

定数

デフォルト値

NOT NULL

サンプルコード

%TYPE / %ROWTYPEについて

%TYPE

%ROWTYPE

サンプルコード

RECORD型（複合型）について

RECORD型

サンプルコード

複合型のユーザ定義について

複合型のユーザ定義

サンプルコード

複合型のユーザ定義

サンプルコード

入れ子になっている複合型のユーザ定義

サンプルコード

複合型のユーザ定義

サンプルコード

配列について

配列の宣言

配列の初期化

配列の値の代入

サンプルコード

パターン1：スカラ型の配列

パターン2：複合型のユーザ定義の配列

関数引数の宣言について

関数引数の宣言

別名の宣言方法

宣言方法１

宣言方法２

サンプルコード

基本的な文

代入について

代入

サンプルコード

PERFORMについて

PERFORM

サンプルコード

SELECT INTOについて

SELECT INTO

SELECT INTO STRICT

サンプルコード

パターン1：レコードを１件取得する場合

パターン2：レコードを0件取得する場合

パターン3：レコードを2件以上取得する場合

INSERT / UPDATE / DELETE RETURNINGについて

INSERT / UPDATE / DELETE RETURNING

INSERT / UPDATE / DELETE RETURNINGについて

サンプルコード

パターン1:STRICT無し / 更新件数２件

パターン2:STRICT有り / 更新件数0件

EXECUTEについて

`DECLARE`

`BEGIN`

`EXCEPTION`

`END;`

`AS $$　〜　$$`

`LANGUAGE plpgsql;`

サンプルコード　

パターン1:`STRICT`無し / 更新件数２件

パターン2:`STRICT`有り / 更新件数0件

`SELECT INTO`の場合

`PERFORM`の場合

`UPDATE`の場合

`FETCH`の場合

`MOVE`の場合

`RETURN QUERY`の場合

戻り値宣言　／　OUTパラメータが存在しない場合

`SETOF sometype`の場合

`TABLE(columns)`の場合