PostgreSQL プロシージャのサンプル(PL/pgSQL)

nas2020/12/06

PostgreSQLのPL/pgSQLのストアドプロシージャのサンプルです。

確認環境
・PostgreSQL 11.2
・Windows 10

目次

サンプル 文字列を出力する
  select intoで項目を取得する
  select intoで複数項目を取得する
  select文でカーソル(cursor)とfetchとloopを使用する
  returnで処理を終了する
  NULLで処理を何もしない
確認 プロシージャの存在確認を行う
削除 プロシージャを削除する

前提

PostgreSQLをインストールしています。
PostgreSQLをインストールする手順(Windows)

文字列を出力する

文字列を出力します。

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
  str1 VARCHAR(20);
BEGIN
  str1 := 'こんにちは';
  RAISE INFO 'msg：%', STR1; --'msg：こんにちはと出力される
END;
$$
LANGUAGE plpgsql;

4行目は、変数を宣言しています。
6行目は、変数に値をセットしています。
7行目は、変数の値を出力します。コメントはハイフン2つ続けます。
10行目は、言語の指定で必要です。

プロシージャを実行する

上記で作成したプロシージャをpsqlで実行するサンプルです。
call プロシージャ名()で実行します。

call test1();

 

select intoで値を取得する

入力として数値を渡してsql intoで値を取得します。

CREATE OR REPLACE PROCEDURE test1(id1 int) 
AS $$
DECLARE
  str1 VARCHAR(20);
BEGIN
  select name into str1 from SYAIN
  where ID = id1;
  RAISE INFO 'msg：%', STR1; --'msg：鈴木と出力される
END;
$$
LANGUAGE plpgsql;

1行目は、引数としてintの値を受け取ります。
6行目はselect intoで取得したnameを右の変数のstr1にセットしています。
7行目は、外部から受け取った数値で条件を指定します。

プロシージャを実行する

上記で作成したプロシージャをpsqlで実行するサンプルです。
引数として1を指定しています。

call test1(1);

 

select intoで複数項目を取得する

DECLARE
  str1 VARCHAR(20);
  str2 VARCHAR(20);  
BEGIN
  select name,romaji into str1,str2 from SYAIN
  where ID = id1;
  RAISE INFO 'msg：%,%', STR1,STR2; --'msg：鈴木 suzukiと出力される
END;

selectで取得する項目を複数にし、intoの後の変数も複数にすれば複数項目を取得できます。

 

select文でカーソル(cursor)とfetchとloopを使用する

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
  cur1 CURSOR FOR
	select name from SYAIN;
	syain_rec RECORD;
BEGIN
  OPEN cur1;
  LOOP
    FETCH cur1 INTO syain_rec;
      EXIT WHEN NOT FOUND;
    RAISE INFO '%', syain_rec.name;
  END LOOP;
  CLOSE cur1;
END;
$$
LANGUAGE plpgsql;

プロシージャを実行する

上記で作成したプロシージャをpsqlで実行するサンプルです。

call test1();

 

returnで処理を終了する

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
  num1  int;
BEGIN
  num1 := 1;
  if num1 = 1 then
    RAISE INFO '処理1'; --'出力される
    return;
  end if;
  RAISE INFO '処理2';
END;
$$
LANGUAGE plpgsql;

9行目は、returnです。このプロシージャの処理が終了します。
実行した場合、8行目の「処理1」が出力されます。
11行目の「処理2」は出力されません。

 

NULLで処理を何もしない

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
	num1  int;
BEGIN
  num1 := 1;
  if num1 = 1 then
     NULL;
  end if;
  RAISE INFO '処理2';--'出力される
END;
$$
LANGUAGE plpgsql;

NULLを記述すると何もしません。
NULLがない場合でもエラーにはなりません。

 

プロシージャの存在確認を行う

SELECT proname,proowner,pronargs,prosrc FROM pg_proc 
WHERE proname = 'test1';

pg_procテーブルに情報があります。

https://www.postgresql.jp/document/9.0/html/catalog-pg-proc.html

 

プロシージャを削除する

DROP PROCEDURE test1();
DROP PROCEDURE test1(int);

プロシージャに引数がある場合は、引数のデータ型を指定します。

関連の記事

PostgreSQL DDL文でtable,view等を作成する

△上に戻る