English 中文(简体)
邮局15.4 jsonb_to_recordset and jsonb_populate_recordset
原标题：A strange problem with PostgreSQL 15.4 jsonb_to_recordset and jsonb_populate_recordset

I have a postgresql function that takes a number of parameters, the last of which is jsonb. It s called with the following value of parameters (double quotes due to postgres diagnostic output):

SELECT vspm.rfd_add_f
(
  $1::bigint
  , $2::bigint
  , $3::bigint
  , $4::timestamptz
  , $5::timestamptz
  , $6::bigint
  , $7::bigint
  , $8::text
  , $9::text
  , $10::timestamptz
  , $11::text
  , $12::jsonb
)
","parameters: $1 = NULL, $2 =  1 , $3 =  18014398509482060 , $4 = NULL, $5 = NULL, $6 =  18014398509484942 , $7 =  18014398509482082 , $8 = NULL, $9 = NULL, $10 = NULL, $11 = NULL, $12 =  [{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489518"",""quantity"":""111000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-11T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null},{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489524"",""quantity"":""222000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-12T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null},{""id"":null,""modUserId"":null,""validFrom"":null,""validTo"":null,""clientId"":null,""client"":null,""rfdId"":null,""rfd"":null,""rfdNumber"":null,""rfdDate"":null,""managerId"":null,""manager"":null,""estimateId"":null,""estimate"":null,""productionNeedId"":""18014398509489530"",""quantity"":""333000000"",""sortOrder"":null,""wantedDeliveryDate"":""2023-12-13T18:00:00Z"",""objectId"":null,""object"":null,""section"":null,""nomenclatureId"":null,""nomenclature"":null,""muId"":null,""mu"":null,""remarks"":null}] ",,,,,,,,"PostgreSQL JDBC Driver","client backend",,0

在这方面,以下领域意义重大:

[
    {
        "productionNeedId": "18014398509489518"
        , "quantity": "111000000"
        , "wantedDeliveryDate": "2023-12-11T21:00:00+03:00"
    }
    , {
        "productionNeedId": "18014398509489524"
        , "quantity": "222000000"
        , "wantedDeliveryDate": "2023-12-12T21:00:00+03:00"
    }
    , {
        "productionNeedId": "18014398509489530"
        , "quantity": "333000000"
        , "wantedDeliveryDate": "2023-12-13T21:00:00+03:00"
    }
]

在进行缩编的职能开始时,有以下声明:

RAISE LOG  ==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = % 
  , (pitems::jsonb->0)::jsonb->> quantity 
  , (pitems::jsonb->0)::jsonb->> productionNeedId 
  , (pitems::jsonb->0)::jsonb->> wantedDeliveryDate 
;
RAISE LOG  ==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = %   
  , (pitems::jsonb->1)::jsonb->> quantity 
  , (pitems::jsonb->1)::jsonb->> productionNeedId 
  , (pitems::jsonb->1)::jsonb->> wantedDeliveryDate 
;
RAISE LOG  ==> rfditem_add_many_f: json quantity = %, productionNeedId = %, wantedDeliveryDate = %   
  , (pitems::jsonb->2)::jsonb->> quantity   
  , (pitems::jsonb->2)::jsonb->> productionNeedId 
  , (pitems::jsonb->2)::jsonb->> wantedDeliveryDate 
;

我获得正确的产出:

"==> rfditem_add_many_f: json quantity = 111000000, productionNeedId = 18014398509489518, wantedDeliveryDate = 2023-12-11T18:00:00Z",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 20 at RAISE


"==> rfditem_add_many_f: json quantity = 222000000, productionNeedId = 18014398509489524, wantedDeliveryDate = 2023-12-12T18:00:00Z ",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 25 at RAISE


"==> rfditem_add_many_f: json quantity = 333000000, productionNeedId = 18014398509489530, wantedDeliveryDate = 2023-12-13T18:00:00Z ",,,,,"PL/pgSQL function rfditem_add_many_f(bigint,bigint,timestamp with time zone,timestamp with time zone,bigint,timestamp with time zone,text,jsonb) line 30 at RAISE

But when I further try to process this jsonb with the jsonb_to_recordset function, the values of fields productionNeedId 以及 wantedDeliveryDate are not found.

FOR lrecord IN
  SELECT
    id, moduserId, validFrom, validTo, clientId, rfdId, productionNeedId, quantity, sortOrder, wantedDeliveryDate, remarks
  FROM jsonb_to_recordset(pitems) AS (id bigint, moduserId bigint, validFrom timestamptz, validTo timestamptz, clientId bigint, rfdId bigint, productionNeedId bigint, quantity bigint, sortOrder int, wantedDeliveryDate timestamptz, remarks text)
LOOP
      RAISE LOG  ==> rfditem_add_many_f read : validFrom = %, validTo = %, clientid = %, rfdId = %, productionNeedId = %, quantity = %, sortOrder = %, wantedDeliveryDate = %, remarks = % 
        , coalesce(lrecord.validFrom,  1900-01-01 ::timestamptz)
        , coalesce(lrecord.validTo,  1900-01-01 ::timestamptz)
        , coalesce(lrecord.clientId, -1)
        , coalesce(lrecord.rfdId, -1)
        , coalesce(lrecord.productionNeedId, -1)
        , coalesce(lrecord.quantity, -1)
        , coalesce(lrecord.sortOrder, -1)
        , coalesce(lrecord.wantedDeliveryDate,  1900-01-01 ::timestamptz)
        , coalesce(lrecord.remarks,   )
      ;

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = -1, productionNeedId = -1, quantity = 111000000, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

j子阵列的其余两个部分没有处理,因为后来,在试图在数据库表上添加记录时,发生了侵犯廉正的制约因素。

我还利用以下职能:

FOR lrecord IN
  SELECT
    validFrom, validTo, clientId, rfdId, productionNeedId, quantity, sortOrder, wantedDeliveryDate, remarks
  FROM jsonb_populate_recordset(null::rfditem_type, pitems)
LOOP

声明为:

CREATE TYPE rfditem_type AS
(
  id                    bigint

  , moduserId           bigint
  , validFrom           timestamptz
  , validTo             timestamptz

  , clientId            bigint

  , rfdId               bigint
  , productionNeedId    bigint
  , quantity            bigint

  , sortOrder           integer

  , wantedDeliveryDate  timestamptz

  , remarks             text
);

但结果更糟(下降的产出相同):

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 111000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

...
"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 222000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "
...
"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = 333000000, productionNeedId = -1, quantity = -1, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

数量值被分配到 r 在Id领域,其他领域没有签署。

以及

FOR lrecord IN
  SELECT * from jsonb_populate_recordset(null::rfditem_type, pitems)
LOOP

在此情况下,结果略有不同:

"==> rfditem_add_many_f read : validFrom = 1900-01-01 00:00:00+02:30:17, validTo = 1900-01-01 00:00:00+02:30:17, clientid = -1, rfdId = -1, productionNeedId = -1, quantity = 111000000, sortOrder = -1, wantedDeliveryDate = 1900-01-01 00:00:00+02:30:17, remarks = "

that is, the value for the quantity field was assigned correctly, but the fields productionNeedId 以及 wantedDeliveryDate were not found

我的想法是,这可能是由于外地名称中的信件的不同情况,但为什么在诊断产出中一切都正确发挥作用? (我确信,在编辑背书之前就是这种情况。)

问题回答

是的,这几乎肯定是由于不同的搭车。 当你不引用你的识别资料时,邮政局将这些人视为较低者,因此不会发现他们。 使用<代码>->>,将财产名称作为示意图,没有问题。

因此使用

FOR lrecord IN
  SELECT *
  FROM jsonb_to_recordset(pitems) AS ("id" bigint, "moduserId" bigint, "validFrom" timestamptz, "validTo" timestamptz, "clientId" bigint, "rfdId" bigint, "productionNeedId" bigint, "quantity" bigint, "sortOrder" int, "wantedDeliveryDate" timestamptz, "remarks" text)
LOOP
  RAISE LOG  ==> rfditem_add_many_f read : validFrom = %, validTo = %, clientid = %, rfdId = %, productionNeedId = %, quantity = %, sortOrder = %, wantedDeliveryDate = %, remarks = % 
    , coalesce(lrecord."validFrom",  1900-01-01 ::timestamptz)
    , coalesce(lrecord."validTo",  1900-01-01 ::timestamptz)
    , coalesce(lrecord."clientId", -1)
    , coalesce(lrecord."rfdId", -1)
    , coalesce(lrecord."productionNeedId", -1)
    , coalesce(lrecord."quantity", -1)
    , coalesce(lrecord."sortOrder", -1)
    , coalesce(lrecord."wantedDeliveryDate",  1900-01-01 ::timestamptz)
    , coalesce(lrecord."remarks",   )
  ;
时间：2023-09-22 17:06:04

是的,在json房地产名称的情况下,问题无疑是存在的。 在对背景进行重新设计并只在较低的情况下开始移交这些名字之后,所有工作都是有效的。

I would like to wish the Postgresql team greater consistency in functions and operators for working with json. If the properties in the first diagnostic output ((pitems::jsonb->0)::jsonb->> productionNeedId ) were not located in the same way as subsequently in the functions jsonb_to_recordset and jsonb_populate_recordset, the problem would have been identified and solved much earlier

时间：2023-09-25 02:56:24


上一篇：
下一篇：


相关问题
摘录数据

我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。

时间：2009-11-17 09:07:52 回答：9 展示：1
Postgres dump of only parts of tables for a dev snapshot

On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...

时间：2009-11-16 21:54:33 回答：3 展示：1
How to join attributes in sql select statement?

I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.

时间：2009-11-12 17:16:55 回答：9 展示：1
What text encoding to use?

I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...

时间：2009-11-06 17:17:15 回答：2 展示：1
SQL LIKE condition to check for integer?

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...

时间：2009-11-05 23:06:18 回答：10 展示：1
热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全