【PostgreSQL】動的にSQLを組み立てる際、ドルクォートを使ってシンタックスハイライトが効くように実装する

概要

Function内で条件分岐してSQLを動的に組み立てる際、単に文字列を連結するとシンタックスハイライトが効かない。ただでさえ動作が複雑になるのにSQLも読みづらくなって死人が出るので、シンタックスハイライトを効かせながらスマートにSQLを動的に組み立てる処理を作ってみます。

具体的には、ドルクォートを使ってSQLを括って定義してあげると、シンタックスハイライトが効くようになります。

項目	内容
取り扱う内容	・ PostgreSQLのFunction内で、シンタックスハイライトを効かせながら動的にSQLを構築する方法 ・ 引数に応じて発行するSQLを変更するFunctionの簡単な事例
想定読者	・ PostgreSQLでFunctionの実装を行う人
ゴール	・ 文字列の連結を用いずに、シンタックスハイライトが効く状態で、現実的にメンテ可能なFunctionの定義ができる

前提となるデータの投入

テーブル構成：

• m_product（商品マスタ）：商品のidと名称を管理する
• m_warehouse（倉庫在庫テーブル）：各商品の在庫数を管理する
• m_arrival（入荷予定テーブル）：予定されている入荷数を管理する
  

テーブル定義・データ投入SQL

-- 今回使うスキーマの定義
CREATE SCHEMA IF NOT EXISTS shop;

-- 商品マスタ
CREATE TABLE shop.m_product (
    id integer PRIMARY KEY,
    name text NOT NULL
);

-- 倉庫在庫テーブル
CREATE TABLE shop.t_warehouse (
    product_id integer PRIMARY KEY REFERENCES shop.m_product(id),
    quantity integer NOT NULL
);

-- 入荷予定テーブル
CREATE TABLE shop.t_arrival (
    product_id integer PRIMARY KEY REFERENCES shop.m_product(id),
    quantity integer NOT NULL
);

-- サンプルデータ投入
INSERT INTO shop.m_product (id, name) VALUES
    (1, 'きのこ'),
    (2, 'たけのこ'),
    (3, 'きのこ　ダークチョコ'),
    (4, 'たこのこ　ホワイトチョコ');

-- 通常商品の在庫データ
INSERT INTO shop.t_warehouse (product_id, quantity) VALUES
    (1, 10),
    (2, 20);

-- 予約商品の入荷予定データ
INSERT INTO shop.t_arrival (product_id, quantity) VALUES
    (3, 50),
    (4, 10);

-- 投入済データの確認
SELECT * FROM shop.m_product ORDER BY id;
SELECT * FROM shop.t_warehouse ORDER BY product_id;
SELECT * FROM shop.t_arrival ORDER BY product_id;

Functionの実装：引数に応じて、在庫のある商品または入荷予定の商品を検索する

機能・呼び出した結果

normal（在庫商品）とreserve（予約商品）の2通りを引数product_typeにとるFuncitonを実装します。渡された引数の値に応じて、いま在庫のある商品と、入荷予定のある商品のリストを返します。在庫商品と入荷予定の商品は別テーブルにあるので、SELECT文のJOIN句を引数に応じて書き分ける必要があります。

在庫商品を取得する呼び出し方

SELECT * FROM shop.f_product_stock_info('normal');

id	name	quantity
1	きのこ	10
2	たけのこ	20

予約商品を取得する呼び出し方

SELECT * FROM shop.f_product_stock_info('reserve');

id	name	quantity
3	きのこ　ダークチョコ	50
4	たこのこ　ホワイトチョコ	10

文字列だけで動的にSQLを組み立てたパターン：見づらい方

ドルクォートを使わないでFunnctionを実装してみます。受け取った引数にて在庫商品 or 予約商品の各テーブルへSELECT文を出し分ける実装になっています。SQL部分は文字列として扱っているので、INNER JOINや=など、列名やテーブル名と区別して読みたい部分も全部同色になって読みづらいですね。慣れている人は「こんなもんやろ」ってお思いかと思いますので、続けて読んでってください。

-- Function定義（クォーテーション版）
CREATE OR REPLACE FUNCTION shop.f_product_stock_info(
    product_type text
) RETURNS TABLE (
    id integer,
    name text,
    quantity integer
) AS $$
DECLARE
     -- ベースになるSQL 引数にかかわらず固定で実行されるところ
    -- /*{JOIN_CONDITION}*/ の部分は、後でJOIN句に置き換えられる
    -- もちろん、もっとシンプルにIF/ELSIFで分岐する形でもOK
    base_query text := '
    SELECT 
        p.id,
        p.name,
        m.quantity
    FROM shop.m_product p
    /*{JOIN_CONDITION}*/
    ';

    -- 引数のパターンをキーとして、対応するJOIN句の文字列を保持するJSONを定義
    join_conditions jsonb := jsonb_build_object(
        'normal', '
            INNER JOIN shop.t_warehouse m 
            ON p.id = m.product_id 
        ',
        'reserve', '
            INNER JOIN shop.t_arrival m 
            ON p.id = m.product_id 
        '
    );

    final_query text := '';

BEGIN -- 定数と引数を使った処理の開始
    -- 実行するSQLの組立　base_queryをJOIN句で置換する
    final_query := replace(
        base_query, 
        '/*{JOIN_CONDITION}*/', 
        join_conditions ->> product_type
    );
    
    -- 最終的なSQL文の表示　デバッグ用
    RAISE NOTICE 'Executing query: %', final_query;

    -- SQLの実行と結果返却
    RETURN QUERY EXECUTE final_query;
END;
$$ LANGUAGE plpgsql;

ドルクォートを使ったパターン：見やすい方

連結されるSQLの一部となる文字列について、シングルクォート（''）ではなくドルクォート（\$q$）を使っています。これにより、SQLが単一の文字列とならずに、SQLっぽくシンタックスハイライトが効くようになります。SQL部分が全部のっぺりと一色のコードになっていたぜんこうの実装よりは、たいへん見やすくなっていると思いませんか。

CREATE OR REPLACE FUNCTION shop.f_product_stock_info(
    product_type text
) RETURNS TABLE (
    id integer,
    name text,
    quantity integer
) AS $$
DECLARE -- 定数定義
    -- ベースになるSQL 引数にかかわらず固定で実行されるところ
    -- /*{JOIN_CONDITION}*/ の部分は、後でJOIN句に置き換えられる
    base_query text := $q$
    SELECT 
        p.id,
        p.name,
        m.quantity
    FROM shop.m_product p
    /*{JOIN_CONDITION}*/
    $q$;

    -- 引数のパターンをキーとして、対応するJOIN句の文字列を保持するJSONを定義
    join_conditions jsonb := jsonb_build_object(
        'normal', $q$ 
            INNER JOIN shop.t_warehouse m 
            ON p.id = m.product_id 
        $q$,
        'reserve', $q$ 
            INNER JOIN shop.t_arrival m 
            ON p.id = m.product_id 
        $q$
    );

    final_query text := '';

BEGIN -- 定数と引数を使った処理の開始
    -- 実行するSQLの組立　base_queryをJOIN句で置換する
    final_query := replace(
        base_query, 
        '/*{JOIN_CONDITION}*/', 
        join_conditions ->> product_type -- 引数として受け取ったproduct_typeをキーとしてJOIN句を取り出し、replace()への引数として渡す
    );

    -- 最終的なSQL文の表示　デバッグ用
    RAISE NOTICE 'Executing query: %', final_query;

    -- SQLの実行と結果返却
    RETURN QUERY EXECUTE final_query;
END;
$$ LANGUAGE plpgsql;

後始末：テーブルの削除とか

DROP FUNCTION IF EXISTS shop.f_product_stock_info(text);
DROP TABLE IF EXISTS shop.t_arrival;
DROP TABLE IF EXISTS shop.t_warehouse;
DROP TABLE IF EXISTS shop.m_product;
DROP SCHEMA IF EXISTS shop;

検討事項：WHERE句を動的にするには？

同様にSQLの一部を置き換える方式でいけると思います。ただ、WHERE句無しの場合や複数条件があり得る場合に備えて、base_queryにはWHERE 1=1をつける必要がありそう。動的に追加される検索条件はANDから始めて繋いでいく形ですね。