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

CREATEとCREATE OR REPLACE 違い

項目	説明	備考
CREATE	ストアドを新規作成する
CREATE OR REPLACE	ストアドを新規作成もしくは、更新する	注意※
※引数が違った場合、更新のつもりでも新規でストアドプロシージャが登録されるので注意が必要

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 : "$$ declare ●●"またはその近辺で文字列のドル引用符が閉じていません	A5M2等で実行している場合、実行モードが「プロシージャモード」で実行されているか
モード別の解説ページ：https://a5m2.mmatsubara.com/help/SQLEditor/runSql.html

削除方法

DROP PROCEDURE test1();

参考

変数

変数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 列名 INTO 変数名 FROM テーブル名 WHERE 条件文;
• SELECT 列名A,列名B INTO 変数名A,変数名B FROM テーブル名 WHERE 条件文;

うまくいかないとき

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

上手くいかないとき

症状	確認事項	備考
SQL : structure of query does not match function result type	型が違っていないか 【注意】Character形とtext型は別なので、castが必要 →たとえ一列でも戻す列の型が違っていると、エラーになる。	変数定義箇所
集計関数の型が違う	ROW_NUMBER() → 〇BIGINT ×INTEGER	https://www.postgresql.jp/document/8.4/html/functions-window.html
集計関数のCASTがうまくいかない 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またはその近辺で構文エラー	ステートメントの最後の「;」が抜けている 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 is NULL THEN	WHEN rec_num = NULL THEN

if 使用例

IF delete_flag=0 THEN
	TRUNCATE TABLE cooking_tmp;
END IF;

ポイント	説明	備考
TRUNCATE TABLE テーブル名	テーブル名のデータを削除する