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

nas2020/12/06

PostgreSQLのPL/pgSQLのストアドプロシージャのサンプルです。
(確認環境:Windows 10,PostgreSQL 11.2)

目次

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

文字列を出力する

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
  str1 VARCHAR(20);
BEGIN
  str1 := 'hello world';
  RAISE INFO 'msg：%', STR1; --'msg：hello worldが出力される
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 employee
  where ID = id1;
  RAISE INFO 'msg：%', STR1; --'msg：鈴木
END;
$$
LANGUAGE plpgsql;

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

プロシージャを実行する

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

call test1(1);

 

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

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

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

 

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

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

4行目のCURSORで複数行を取得しています。
9,10行目のLOOPとFETCHで1行ずつ取り出しています。

プロシージャを実行する

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

call test1();

 

returnで処理を終了する

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
  num1  int;
BEGIN
  num1 := 1;
  if num1 = 1 then
    RAISE INFO 'process1'; --'process1
    return;
  end if;
  RAISE INFO 'process2';
END;
$$
LANGUAGE plpgsql;

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

 

NULLで処理を何もしない

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
	num1  int;
BEGIN
  num1 := 1;
  if num1 = 1 then
     NULL;
  end if;
  RAISE INFO 'process2';--'process2
END;
$$
LANGUAGE plpgsql;

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

 

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

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

プロシージャ名が同じでも引数が異なると別になります。
prosrcはソースコードで大量に出る場合があるので項目に入れなくて大丈夫です
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等を作成する

△上に戻る