PostgreSQLJson型とストアドファンクションの受け渡し

私はフリーITエンジニアとして、業務分析、業務改善提案を含む設計、製造、受入検査を含む検査、システム運用開始後の運用改善提案など、様々な分野を担当しています。

今回はPostgreSQLの話題です。

PostgreSQLではJson型があります。
非定型なデータを1つの列に保存するのに適していますが、定型的な業務ではテーブルの列にJson型が適している例は少ないと思います。

ただし、ストアドファンクションとのデータの受け渡しには威力を発揮します。
多数のデータを1度に登録したいときなど、INSERT文をループで回すのは、パフォーマンスもよくありません。
データを1度に渡してしまい、ストアドファンクション内でループさせることで良好なパフォーマンスを得られます。
そんなときにJson型が活躍します。

受け渡すデータはこんな感じです。

これをJSON配列に変換するとこんな感じになります。

[
{
"item_code": "A0001",
"product_name": "KEYBORD",
"req_qty": 10.283
},
{
"item_code": "A0002",
"product_name": "MONITOR",
"req_qty": 526.267
},
{
"item_code": "A0003",
"product_name": "MOUSE",
"req_qty": 610.39
},
{
"item_code": "A0004",
"product_name": "MAIN BOX",
"req_qty": 341.751
},
{
"item_code": "A0005",
"product_name": "USB HUB",
"req_qty": 371.524
},
{
"item_code": "A0006",
"product_name": "ROOTER",
"req_qty": 1395
},
{
"item_code": "A0007",
"product_name": "WINDOWS10Pro",
"req_qty": 1287
},
{
"item_code": "A0008",
"product_name": "VISUAL STUDIO 2016 PRO",
"req_qty": 25
}
]

テスト用のストアドファンクションのソースです。

/*
test_sp_json

input parameters

p_json json
-- Json of -------
      item_code character varying
      ,product_name character varying
      ,req_qty numeric
-------------------

*/
/*
output（none）
*/
CREATE
  OR REPLACE
FUNCTION test_sp_json
(
p_json json
)
RETURNS integer
 AS
$$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT
      *
    FROM
      json_to_recordset(p_json)
      AS rec
      (
          item_code character varying
          ,product_name character varying
          ,req_qty numeric
      )
  LOOP
    -- 本体
    RAISE NOTICE 'item_code = %, product_name = %, req_qty = %',
            rec.item_code, rec.product_name, rec.req_qty;
  END LOOP;
  
  RETURN 0;
END
$$
language 'plpgsql'
;

Json型で受けたパラメーター p_json を
json_to_recordset(p_json) でレコードセットに変換してループさせる処理になります。
ループの中にやりたい処理を書いてください。
ちなみにこの処理を実行するSQLは次のようになります。

SELECT * FROM test_sp_json('
[
 {
   "item_code": "A0001",
   "product_name": "KEYBORD",
   "req_qty": 10.283
 },
 {
   "item_code": "A0002",
   "product_name": "MONITOR",
   "req_qty": 526.267
 },
 {
   "item_code": "A0003",
   "product_name": "MOUSE",
   "req_qty": 610.39
 },
 {
   "item_code": "A0004",
   "product_name": "MAIN BOX",
   "req_qty": 341.751
 },
 {
   "item_code": "A0005",
   "product_name": "USB HUB",
   "req_qty": 371.524
 },
 {
   "item_code": "A0006",
   "product_name": "ROOTER",
   "req_qty": 1395
 },
 {
   "item_code": "A0007",
   "product_name": "WINDOWS10Pro",
   "req_qty": 1287
 },
 {
   "item_code": "A0008",
   "product_name": "VISUAL STUDIO 2016 PRO",
   "req_qty": 25
 }
]
');

実際には、プログラムから渡すので、こんなに長いことはないですが、ストアドファンクションのテスト用の書き方の例として捉えてください。
実行結果は次のようになります。

NOTICE: item_code = A0001, product_name = KEYBORD, req_qty = 10.283
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0002, product_name = MONITOR, req_qty = 526.267
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0003, product_name = MOUSE, req_qty = 610.39
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0004, product_name = MAIN BOX, req_qty = 341.751
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0005, product_name = USB HUB, req_qty = 371.524
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0006, product_name = ROOTER, req_qty = 1395
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0007, product_name = WINDOWS10Pro, req_qty = 1287
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE
NOTICE: item_code = A0008, product_name = VISUAL STUDIO 2016 PRO, req_qty = 25
CONTEXT: PL/pgSQL関数test_sp_json(json)の18行目の型RAISE

Total query runtime: 16 msec
1 行検索しました