PostgreSQLはリファレンスが非常に充実していますが、サポートする機能が多彩であるため個別のケースの挙動が自明ではないことがあります。
ストアド関数は複雑なケースを扱うことが多く、挙動の観察が重要です。
呼び出し形式
ストアド関数の呼び出しはSELECT some_function();で実行できます。
ただしテーブルを返す関数の場合には、この形式で呼び出すとカラム名が割り当てられないケースがあります。
SELECT id, name FROM some_function();のようにFROM句で関数を呼び出すと、戻り値のテーブルにカラム指定できます。
早期リターン
PL/pgSQLのRETURNは他の言語と異なる挙動をとる場合があるため、基本的に注意が必要です。
注意したうえで、以下のような早期リターンは実装可能です。
CREATE FUNCTION get_inventories(IN in_id integer)
RETURNS TABLE (name text, quantity integer) AS $$
BEGIN
PERFORM 1 FROM privileges p
WHERE p.id = in_id;
IF NOT FOUND THEN
RETURN;
END IF;
RETURN QUERY SELECT
i.name AS name,
i.qty AS quantity,
FROM inventories i;
END;
$$ LANGUAGE plpgsql;
テーブルを返すストアド関数では、CREATE FUNCTION ... RETURNS TABLEの定義と、RETURN QUERYの返す型と列名を一致させる必要があります。
よって、目的外のテーブルの値を返したい場合の簡潔な方法はありません。
この例では冒頭で別のテーブル（アクセス権限照会など）を検索し、引数にマッチするレコードがない場合にRETURNしています。
この例では、大方の期待どおり早期リターンの挙動となります。
他のテーブルから定義に一致する型をとり出すことは難しいのですが、戻り値をセットしない場合には空行となり、型不一致エラーを避けられます。
また、引数をとるRETURNの中には関数処理を中断しない（一般的な想像どおりでない）挙動の文もありますが、この例の書き方であればその場で関数を抜ける挙動をとります。
つまり書き方によっては早期リターンに見えるクエリが早期リターンしないことがあるものの、注意すれば早期リターンは実装可能です。
⁋ 2022/09/22↻ 2022/09/23
sqldef は RDBMS のスキーマを config に沿って更新する CLI ツールです。
先行実装 ridgepole の Go クローンと言えます。
Web フレームワークで標準的なマイグ …
PostgreSQLの COPY コマンドは、テーブルデータをCSVファイルなどの形式でインポート／エクスポートする機能です。
COPYと\copyがあり、COPYはサーバー側、\copyはpsqlク …
PostgreSQLのパーティショニングはバージョンアップごとに強化されてきています。
アプリケーションに合わせてデータアクセスを局所化できれば、データが成長しても性能を維持しやすくなります。
パーテ …
複数行を一括でINSERTする際、配列を利用できます。配列型のカラムではなく、複数行を投入する方法です。
PostgreSQLにはunnest()という関数があり、配列を行に変換できます。
たとえば、 …
DBMSにはユニークインデックスがあり、重複レコードをINSERTしようとするとエラーで保護する機能があります。
そして最低限、主キーはユニークです。
テストなどの場合、主キーも含めて同じデータをセッ …
ネストしたテーブル設計では、1:Nの構造になるレコードが頻発します。
その場合の素朴なSQLは以下のような記述になるでしょう。
SELECT categories.name, …
ストアドプロシージャ、ストアドファンクションは、DBに登録(stored)する関数で、PostgreSQLの標準機能を利用する場合、PL/pgSQLで書きます。
ストアドプロシージャの使いどころ スト …
特定のセットアップデータに依存するテストを実行したい場合、テストランナーから直接データベースを操作する構成が手軽です。
JestなどJavascriptプロジェクトでは、node-postgres を …
RustでDB接続するcrate(Rustパッケージ)にはいくつかのプロダクトがありますが、非同期処理を重視した非ORマッパーのcrateとしては、tokio-postgresを使えます。
接続 …
PostgreSQlのロジカルレプリケーションは、ALTER PUBLICATION / ALTER SUBSCRIPTIONを利用して、運用開始後にテーブル追加などの変更を行えます。
（セットアップ …
PostgreSQLのアップグレードには、公式ツール pg_upgrade を利用できます。
ただし、pg_upgradeを実行する前提に以下の条件があり、 公式コンテナイメージ では実行できません。 …
ロジカルレプリケーション機能を利用すると、テーブル単位でレプリケーション構成をとれます。
一般的なRead/Writeスプリット構成のほか、アプリケーションフレームワークから見て単一DB内に外部マスタ …
レコード数の多いアプリケーションの場合、データベースの一部を切り出して取得したいケースがあります。
このような場合、一度バックアップ用のミニテーブルを作成する手順が手軽です。
PostgreSQLのバ …
PostgreSQLやMySQLなどのデータベースのバックアップ＆リストアにはいくつか方法がありますが、SQL形式の論理バックアップを取得する方法が一般的です。
とくに、DBMSを別のサーバーに構築し …
kubernetesでシステム構築する際に、意外に難しいのがデータベースの構築やバックアップ＆リストアです。
DBインスタンスのコンテナを立てることじたいは簡単なのですが、論理バックアップでデータを移 …
EN contents
sqldef is a CLI tool to maintain RDBMS schema along with corresponding configs.
It is a ridgepole …
While PL/pgSQL has LOOP flow for recursive operations, SQL or psql doesn’t have one.
If you want to …
DBMS has the unique index feature that protects against INSERTing duplicate records, and at a …
