时间戳

首页 > 解决方案 > 调用函数传递 JSONB 作为参数 - Postgres

postgresql - 调用函数传递 JSONB 作为参数 - Postgres

问题描述

下面是我写的函数

     CREATE OR REPLACE FUNCTION upd.insert_testdata(spec jsonb[])
     RETURNS void
     LANGUAGE 'plpgsql' AS $BODY$
            
     BEGIN
    --Consider all columns in specialist table as character varying and code column as integer.
        insert into upd.specialist (to_cd, empid, code, booking_status, availability)
           select j.spec->>'to_cd', 
                  j.spec->>'empid', 
                  j.spec->>'code', 
                  j.spec->>'booking_status',
                  j.spec->>'availability' 
           from jsonb_populate_record(spec) j;
        
                
     END;
     $BODY$;

我正在尝试通过以下命令调用函数

    SELECT upd.insert_testdata(
               '{
                "to_cd":"NFG",
                "empid":"test",
                "code":123,
                "booking_status":"Y", 
                "availability":"MTWTFSS"
                 }'::jsonb[]
             );

但是我收到错误，因为格式错误的数组文字

详细信息：意外的数组元素。 SQL状态：22P02

另外我想知道如何在单个 json 变量中插入多条记录/传递多行

标签： postgresqlfunctionjsonbpostgresql-9.6

解决方案

不要传递 json 值的数组，传递 json 数组：

CREATE OR REPLACE FUNCTION upd.insert_testdata( spec jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  --Consider all columns in specialist table as character varying and code column as integer.
  insert into upd.specialist (to_cd, empid, code, booking_status, availability)
  select j.spec->>'to_cd', 
         j.spec->>'empid', 
         (j.spec->>'code')::int, 
         j.spec->>'booking_status', 
         j.spec->>'availability' 
  from jsonb_array_elements(spec) as j(spec);
end;
$BODY$;

如果您不想手动列出所有键，并且您 100% 确定键名始终与表中的列名匹配，则可以使用jsonb_populate_record

CREATE OR REPLACE FUNCTION upd.insert_testdata( spec jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  --Consider all columns in specialist table as character varying and code column as integer.
  insert into upd.specialist (to_cd, empid, code, booking_status, availability)
  select (jsonb_populate_record(null::specialist, j.spec)).*
  from jsonb_array_elements(spec) as j(spec);
end;
$BODY$;

然后像这样使用它：

select upd.insert_testdata('[
                              {"to_cd":"NFG",
                               "empid":"test",
                               "code":123,
                               "booking_status": "Y",
                               "availability":"MTWTFSS"}
                            ]'::jsonb);

如果你想传递多个元素，你可以像这样使用它：

select upd.insert_testdata('[
                              {"to_cd":"NFG",
                               "empid":"test",
                               "code":123,
                               "booking_status": "Y",
                               "availability":"MTWTFSS"},
                              {"to_cd":"CFG",
                               "empid":"test2",
                               "code": 456,
                               "booking_status": "N",
                               "availability":"MT"}
                            ]'::jsonb);

在线示例

推荐阅读