PostgreSQLのストアドプロシージャの書き方

PL/pgSQLってなんだっけ？

PL/pgSQLはPostgreSQL内で使える手続き言語。SQLに制御や計算機能を追加してより便利にしたもの。PosgreSQL内部に閉じて実行できる処理はその中でやってしまった方が通信コストなどが減り効率的。

ストアドプロシージャってなんだっけ？

テーブルへの処理をまとめて実行できる関数みたいなもの

公式Docsはexampleがなくて読みにくいので困ってたがありがたい感じの記事を見つけた。

複数テーブルのレコードを一括で削除するトランザクションの例

code:example.sql

DROP FUNCTION delete_connection_and_conditions;

CREATE OR REPLACE FUNCTION delete_connection_and_conditions(p_id int) RETURNS int AS $$

BEGIN

DELETE FROM public.conditions WHERE connection_id = p_id;

DELETE FROM public.connections WHERE id = p_id;

RETURN p_id;

RAISE EXCEPTION 'Deletion failed'

USING DETAIL = 'Failed to delete',

HINT = 'There is nothing you can do.';

END

$$ LANGUAGE plpgsql SECURITY DEFINER;

メモ

ストアドの引数に多次元配列は使えない

Triggerに設定できるFUNCTIONは特殊な書き方が必要。RETURNSにTRIGGERと指定する。

code:example.sql

DROP FUNCTION IF EXISTS create_profile_from_auth_user();

CREATE OR REPLACE FUNCTION create_profile_from_auth_user() RETURNS TRIGGER

AS $$

BEGIN

INSERT INTO public.profiles(user_id)

VALUES(new.id);

RETURN new;

END

$$ LANGUAGE 'plpgsql';