PostgreSQL ストアドプロシージャ 備忘録

## CREATEとCREATE OR REPLACE 違い
|項目|説明|備考|
|----|----|----|
|CREATE|ストアドを新規作成する||
|CREATE OR REPLACE|ストアドを新規作成もしくは、更新する|注意※<BR>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/637823/2de35fa8-c45e-d57b-7b07-77ab9ede4807.png)|
※引数が違った場合、更新のつもりでも新規でストアドプロシージャが登録されるので注意が必要

## FUNCTIONとPROCEDURE 違い
|項目|説明|
|----|----|
|FUNCTION|値を返す||
|PROCEDURE |値を返さない||

## テストPROCEDURE 
### 作成と登録手順
```:test1
CREATE OR REPLACE PROCEDURE public.test1() 
AS $$
DECLARE
  str1 VARCHAR(20);
BEGIN
  str1 := 'テストですよ。';
  RAISE INFO 'msg：%', STR1; --'msg：テストですよ。と出力される
END;
$$
LANGUAGE plpgsql;
```
|ポイント|説明|
|----|----|
|プロシージャの登録|A5M2等で「test1」を実行すれば、登録が完了する||
|\$\$|登録後「\$」と「\$」の間に自動で「FUNCTION」か「PROCEDURE」が入る||

### 実行手順
* コマンドプロンプトを立ち上げPostgreSQLにログインする
* 「\c]コマンドでストアドプロシージャを登録したDBに変更する
* 「call test1」で実行する

```:実行結果
DB名=# call test1();
INFO:  msg：テストですよ。
CALL
```
### うまくいかない時
|症状|確認ポイント|
|----|----|
|SQL   : "\$\$<br>declare ●●"またはその近辺で文字列のドル引用符が閉じていません|A5M2等で実行している場合、実行モードが「プロシージャモード」で実行されているか|
モード別の解説ページ：https://a5m2.mmatsubara.com/help/SQLEditor/runSql.html
### 削除方法
```
DROP PROCEDURE test1();
```

参考

https://itsakura.com/postgresql-pl-pgsql

## 変数
### 変数strに”こんにちは”と代入する例
```
CREATE OR REPLACE FUNCTION 代入サンプル(
) RETURNS TEXT AS $$
DECLARE
   str TEXT :='こんにちは';
BEGIN
  RETURN str;
END;
$$ LANGUAGE plpgsql;
```
```:呼び出し側
SELECT 代入サンプル();
```

## SQLの結果を変数へ代入
```
CREATE OR REPLACE FUNCTION SQL結果代入()
　　RETURNS integer　
AS $$
DECLARE 
    rec INTEGER;
BEGIN
  SELECT COUNT(id)INTO rec FROM table_sample;
  RETURN rec;
END;
$$ LANGUAGE plpgsql
```
### ポイント
* DECLARE で漏れなく宣言しておくこと
* SELECT 列名 <b>INTO</b> 変数名 FROM テーブル名 WHERE 条件文;
* SELECT 列名A,列名B <b>INTO</b> 変数名A,変数名B FROM テーブル名 WHERE 条件文;

### うまくいかないとき
|エラー内容|原因|修正後|
|----|----|----|
|列名Aは既知の変数ではありません|DECLAREで宣言が漏れている|変数A INTEGER;|
|列名Bは既知の変数ではありません|DECLAREで宣言が漏れている|変数B INTEGER;|
|列名Bは既知の変数ではありません|列名A <b>INTO</b> 変数名A,列名B <b>INTO</b> 変数名B|列名A,列名B <b>INTO</b> 変数名A,変数名B|
|SQL   : "("またはその近辺で構文エラー|変数 ＝ SELECT f_sample(); <br><b>イコールで代入できない。</b>|SELECT f_sample() INTEL 変数;|

## 引数 戻り値 設定
### 引数の値をそのまま 戻す例
```
create or replace function オウム返し(str TEXT)
RETURNS TEXT 
AS $$
  declare res TEXT := str;
begin
  RETURN res;
end;
$$ language plpgsql;
```
```:呼び出し側
SELECT オウム返し('やっほー！');
```
### 上手くいかないとき
|症状|確認事項|備考|
|----|----|----|
|SQL   : 列"やっほー！！"は存在しません|シングルクォーテーションで囲っているか|ダブルクォーテーションで囲うとうまくいかない|


参考
https://qiita.com/ester41/items/3401606322888a0a7f21

### テーブルを戻す例
```:カードテーブル
CREATE TABLE m_card (
  card_id integer not null,
  card_name char(32),
  PRIMARY KEY (card_id)
)
```
### 引数無し　
```
CREATE OR REPLACE FUNCTION f_return_table_sample1(
) RETURNS TABLE(
    r_card_name text
) AS $$
DECLARE
BEGIN
  RETURN QUERY 
  SELECT
        cast(card_name AS TEXT)
  FROM 
        m_card
    ;
END;
$$ LANGUAGE plpgsql;
```
### 引数あり
```
CREATE OR REPLACE FUNCTION f_return_table_sample2(id INTEGER, name TEXT
) RETURNS TABLE(
    --変数定義箇所--
    --tbl.r_neme text ←エラーになる・・・A--
    r_card_name text
) AS $$
DECLARE
BEGIN
  RETURN QUERY 
  SELECT
        cast(card_name AS TEXT)
  FROM 
        m_card
  WHERE
        card_id = id
    ;
END;
$$ LANGUAGE plpgsql;
```
### 上手くいかないとき
|症状|確認事項|備考|
|----|----|----|
|<b>SQL   : structure of query does not match function result type</b>|型が違っていないか<br>【注意】Character形とtext型は別なので、castが必要<br>→<b>たとえ一列でも戻す列の型が違っていると、エラーになる。</b>|変数定義箇所|
|集計関数の型が違う|ROW_NUMBER() → 〇BIGINT ×INTEGER|https://www.postgresql.jp/document/8.4/html/functions-window.html|
|集計関数のCASTがうまくいかない<br>ASの周辺で構文エラー|CAST(集計 AS 型)AS 列名 とする|例：CAST(ROW_NUMBER() OVER(order by Z.nexttime_schedule_numerical)AS INTEGER) AS num|
|SQL   : "."またはその近辺で構文エラー|変数定義箇所はAS句で指定したテーブル名.とできない。|変数定義箇所 A|
|列参照 が一意に特定できません|RETURNS TABLE()内で定義した名前をBEGIN~END内で使うと正しく動作。名前がぶつからない様に修正が必要。|http://aoyagikouhei.blog8.fc2.com/blog-entry-126.html|

## ログ
```:単体
RAISE EXCEPTION 'DEBUG: %', id ;
RAISE 'DEBUG: %', id ;
```
|ポイント|説明|
|----|----|
|%|「%」に「id」が代入される。||

```:複数
RAISE EXCEPTION 'DEBUG: id＝% name=%', id,name ;
```

### 上手くいかないとき
|確認事項|説明|備考|
|----|----|----|
|EXCEPTIONもしくは省略になっているか|DEBUG等のレベルだとデフォルトでは表示されない|レベルを省略すると、EXEPTIONになる。文字通り例外を吐くので処理は中断される。|

## 他の関数を呼び出す
### ビンゴゲームのようにランダムの数値を出す例
```
CREATE OR REPLACE FUNCTION ビンゴ(
) RETURNS INTEGER AS $$
DECLARE
   rand_num INTEGER;
BEGIN
  rand_num:=CAST(random()*100 AS INTEGER);
  RETURN rand_num;
END;
$$ LANGUAGE plpgsql;
```

## 条件分岐
rec_num = 1の時だけ、該当するflag_nexttime_compをTRUEにUPDATEする例

```
CASE	
	WHEN rec_num = 0	THEN
	WHEN rec_num = 1	THEN
		UPDATE housework_history SET flag_nexttime_comp = TRUE WHERE history_id = rec_id;
	WHEN rec_num <= 2 THEN 
	ELSE 
END CASE;
```
### 上手くいかない時
|エラー内容|原因|修正|
|----|----|----|
|WHENまたはその近辺で構文エラー|ステートメントの最後の「;」が抜けている<br>UPDATE housework_history SET flag_nexttime_comp = TRUE WHERE history_id = rec_id|UPDATE housework_history SET flag_nexttime_comp = TRUE WHERE history_id = rec_id;|
|";"またはその近辺で構文エラー|THEN;としてしまっている。|THENの後ろに;は不要|
|";"またはその近辺で構文エラー|「END CASE」の「CASE」が抜けていたり「END」になっている|「END CASE」で括る|


### rec_numがNULLの時
|正|誤|
|----|----|
|WHEN rec_num <b>is</b> NULL THEN|WHEN rec_num <b>=</b> NULL THEN||

### if 使用例
```
IF delete_flag=0 THEN
	TRUNCATE TABLE cooking_tmp;
END IF;
```
|ポイント|説明|備考|
|----|----|----|
|TRUNCATE TABLE テーブル名|テーブル名のデータを削除する||