json - Postgres 函数返回一行作为 JSON 值

Question

我是 PG 的新手，正在尝试从 MSSQL 转换。

我正在开发一个将返回 JSON 结果的函数。这个有效:

Create Or Replace Function ExampleTable_SelectList()
Returns JSON As
$$
  Select array_to_json(array_agg(row_to_json(t))) From
    (Select id, value1, value2, From ExampleTable) t
$$ Language SQL;

现在，我想调用 can update 返回一个值并将该值转换为 JSON 以返回。所以，这个在 set 命令上给出了一个错误。

Create Or Replace Function ExampleTable_Update (id bigint, value1 text)
  Returns JSON As
$$
  Select row_to_json(t) From
  (
    Update ExampleTable
    Set Value1 = value1
    Where id= id
    Returning Value1, Value2;
  ) t
$$ Language SQL;

我怀疑 Postgres 不允许 UPDATE 语句作为子查询。有什么办法吗？

Answer 1

我看到两个主要问题:

1. 您根本无法将UPDATE 放入子查询中。你可以用 data-modifying CTE 解决这个问题喜欢Patrick demonstrates ，但是对于手头的案例来说，这比必要的更昂贵和冗长。

2. 您有一个潜在危险的命名冲突，尚未解决。

更好的查询/功能

暂时将 SQL 函数包装器放在一边(我们将回过头来)。您可以使用带有 RETURNING 子句的简单 UPDATE:

UPDATE tbl
SET    value1 = 'something_new'
WHERE  id = 123
RETURNING row_to_json(ROW(value1, value2));

RETURNING 子句允许涉及更新行的列的任意表达式。这比数据修改 CTE 更短、更便宜。

剩下的问题:行构造函数 ROW(...) 不保留列名(这是一个已知的弱点)，因此您在 JSON 值中获得通用键:

row_to_json
{"f1":"something_new","f2":"what ever is in value2"}

在 Postgres 9.3 中，您需要一个 CTE 另一个函数来封装第一步或转换为定义明确的行类型。详情:

• Return multiple columns of the same row as JSON array of objects

在 Postgres 9.4 中只需使用 json_build_object() or json_object() :

UPDATE tbl
SET    value1 = 'something_new'
WHERE  id = 123
RETURNING json_build_object('value1', value1, 'value2', value2);

或者:

...
RETURNING json_object('{value1, value2}', ARRAY[value1, value2]);

现在您将获得原始列名或您选择的任何键名:

row_to_json
{"value1":"something_new","value2":"what ever is in value2"}

将它包装在一个函数中很容易，这就把我们带到了你的第二个问题......

命名冲突

在您的原始函数中，您对函数参数和列名称使用相同的名称。这通常是一个非常糟糕的主意。您需要深入了解哪个标识符在哪个范围内最先出现。

在手头的案例中，结果完全是胡说八道:

Create Or Replace Function ExampleTable_Update (id bigint, value1 text) Returns 
...
    Update ExampleTable
    Set Value1 = value1
    Where <b>id = id</b>
    Returning Value1, Value2;
...
$$ Language SQL;

虽然您似乎期望 id 的第二个实例会引用函数参数，但事实并非如此。列名在 SQL 语句的范围内排在第一位，第二个实例引用该列。导致表达式始终为 true，但 id 中的 NULL 值除外。因此，您将更新所有行，这可能会导致灾难性的数据丢失。更糟糕的是，您甚至可能直到后来才意识到这一点，因为 SQL 函数将返回 one 任意行，如 RETURNING 子句所定义函数(返回一个行，而不是一组行)。

_{在这种特殊情况下，您会很“幸运”，因为您还有 value1 = value1，它会用其先前存在的值覆盖该列，有效地...什么都不做，在一种非常昂贵的方式(除非触发器做某事)。作为结果，您可能会感到困惑，因为得到的任意行的 value1 没有变化。}

所以，不要。

避免像这样潜在的命名冲突，除非您确切地知道自己在做什么(显然不是这样)。我喜欢的一个约定是在函数中为参数和变量名称添加下划线，而列名称从不以下划线开头。在许多情况下，您可以只使用位置引用来明确:$1、$2，...，但这只回避了问题的一半。只要您避免命名冲突，任何方法都是好的。我建议:

CREATE OR REPLACE FUNCTION foo (_id bigint, _value1 text)
  RETURNS json AS
  LANGUAGE sql
$func$
UPDATE tbl
SET    value1 = _value1
WHERE  id     = _id
RETURNING json_build_object('value1', value1, 'value2', value2);
$func$;

另请注意，这会在 UPDATE 之后返回 value1 中的实际列值，这可能会也可能会与您的输入参数 _value1 不同。可能有数据库规则或触发器干扰......