时间戳

首页 > 解决方案 > 如何使用文字值设置包含 JSONB 元素的复合类型数组？

sql - 如何使用文字值设置包含 JSONB 元素的复合类型数组？

问题描述

我有这种类型：

CREATE TYPE public.user_type AS (
    text_1 VARCHAR(512),
    text_2 VARCHAR(1000),
    jsonb_1 JSONB,
    jsonb_2 JSONB
);

并且需要知道如何格式化文字值来为单元测试设置这种类型的数组。当我尝试设置它时，我不断收到格式错误的数组文字错误。

SQL Error [22P02]: ERROR: malformed array literal: "{{"(text 1,text 2,{"key_1":"value_1"},{"key_2":"value_2"})"}"
  Detail: Unexpected array element.
  Where: PL/pgSQL function inline_code_block line 4 during statement block local variable initialization

用这个片段：

DO $$
DECLARE
    user_type public.user_type[] = '{{"(text 1,text 2,{"key_1":"value_1"},{"key_2":"value_2"})"}';
BEGIN
END $$;

如何形成文字字符串来设置这种复合类型？

对于其他单元测试，我可以声明没有 JSONB 元素的复合类型并使用文字值设置它们。例如这有效：

对于这种类型：

CREATE TYPE public.user_type_2 AS (
    text_1 VARCHAR(512),
    text_2 VARCHAR(1000)
);

此代码段将返回多行：

DO $$
DECLARE
    user_type_2 public.user_type_2[] = '{{"(string_1a,string_1b)"},{"(string_2a,string_2b)"}}';
BEGIN
     DROP TABLE IF EXISTS _results;
     CREATE TEMPORARY TABLE _results AS  
     SELECT * FROM UNNEST(user_type_2) x (text_1, text_2);
END $$;

SELECT * FROM _results;

正如我所料

+-----------+-----------+
|  text_1   |  text_2   |
+-----------+-----------+
| string_1a | string_1b |
| string_2a | string_2b |
+-----------+-----------+

标签： sqljsonpostgresqlunit-testingplpgsql

解决方案

为此，您必须使用几个级别的转义：

  • 数组元素必须被 包围"，因为它包含,,{和}。

  • 数组内部的每个"元素都必须转义为\".

  • "复合类型中的 JSON 元素必须用(now )包围，\"因为它们包含,.

  • "for JSON 字符串必须加倍才能"从前一点转义，因此它们最终变为\"\".

您的作业可能如下所示：

user_type public.user_type[] := '{"(text 1,text 2,\"{\"\"key_1\"\": \"\"value_1\"\"}\",\"{\"\"key_2\"\": \"\"value_2\"\"}\")"}';

呸。

推荐阅读