database - postgres/pl sql generate string 创建额外的双引号

Question

我在 postgres 中使用 plsql 函数来遍历大型数据集，并使用每条记录中的数据生成一个字符串。出于某种原因，我得到了这样不需要的额外双引号:

'{ ""id"": ""1""}'

它应该是这样的

'{ "id": "1"}'

此外，我注意到字符串开头和结尾处有不需要的括号。我使用的代码如下:

CREATE FUNCTION gen_blocks()
    RETURNS TEXT  AS $$ DECLARE
    output TEXT := '';
    j record; BEGIN
    FOR j IN SELECT '{ "id": "' || id ||'"},' LOOP
        output := output || j;
    END LOOP;

    return output; END; $$ LANGUAGE plpgsql;

有什么想法吗？

Answer 1

你的问题就在这里:

output := output || j;

你的j是一个record，不是字符串类型。因此，PostgreSQL 必须为您将您的 record 字符串化。这就是您的报价翻倍的地方。您不想将 j 连接到输出缓冲区，而是想连接 j 中的第一列；一个简单的解决方案是为您的字符串计算添加一个别名，以便您可以通过名称引用它:

FOR j IN SELECT '{ "id": "' || id ||'"},' as s LOOP
    output := output || j.s;
END LOOP;

您也可以跳过自定义函数，只使用 string_agg :

string_agg(expression, delimiter)

input values concatenated into a string, separated by delimiter

像这样:

select string_agg('{ "id": "' || id || '" }', ',') from ...

这将有一个额外的好处，即不在末尾包含一个杂散的逗号(一些 JavaScript 和 JSON 解析器会对此感到不安)。