Postgres函数返回一行作为JSON值 [英] Postgres function returning a row as JSON value

问题描述

我对PG很陌生，并尝试从MSSQL转换.

I am pretty new to PG, and trying to convert from MSSQL.

我正在研究一个将返回JSON结果的函数. 这一项有效:

I am working on a function that will return JSON results. This one works:

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;

现在，我想调用可以更新的方法，该方法返回一个值并将该值转换为JSON以返回.因此，此命令在set命令上给出了错误.

Now, I want to call can update that returns a value and turn that value into JSON to return. So, this one gives an error on the set command.

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语句作为子查询.反正还有吗?

I suspect that Postgres does not allow the UPDATE statement as a subquery. Is there anyway around that?

推荐答案

我看到两个主要问题:
1..您完全不能将UPDATE放入子查询 .您可以使用修改数据的CTE来解决此问题就像 Patrick演示的一样，但这比手头的案件要昂贵和冗长.
2..您有一个潜在危险的命名冲突，尚未解决.

I see two major problems:
1. You cannot put an UPDATE into a subquery at all. You could solve that with a data-modifying CTE like Patrick demonstrates, but that is more expensive and verbose than necessary for the case at hand.
2. You have a potentially hazardous naming conflict, that hasn't been addressed yet.

暂时将SQL函数包装器放在一边(我们将回到这一点).您可以将简单的UPDATE与RETURNING子句一起使用:

Leaving the SQL function wrapper aside for the moment (we'll come back to that). You can use a simple UPDATE with a RETURNING clause:

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

RETURNING子句允许包含更新行的列的任意表达式.这比修改数据的CTE更短，更便宜.

The RETURNING clause allows arbitrary expressions involving columns of the updated row. That's shorter and cheaper than a data-modifying CTE.

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

The remaining problem: the row constructor ROW(...) does not preserve column names (which is a known weakness), so you get generic keys in your JSON value:

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

在Postgres 9.3中，您需要CTE另一个函数来封装第一步或强制转换为定义良好的行类型.详细信息:

In Postgres 9.3 you would need a CTE another function to encapsulate the first step or a cast to a well-defined row type. Details:

• 以JSON对象数组的形式返回SQL(Postgres)

在Postgres 9.4 中，只需使用

In Postgres 9.4 just use 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]);

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

Now you get original column names or whatever you chose as key names:

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

将其包装到函数中很容易，这使我们遇到了第二个问题...

It's easy to wrap this in a function, which brings us to your second problem ...

在原始函数中，对函数参数和列名使用相同的名称.这通常是一个非常不好的主意.您需要深入了解哪个标识符在哪个范围中排在首位.

In your original function you use identical names for function parameters and column names. This is a generally a very bad idea. You would need to understand intimately which identifier comes first in which scope.

在手头的情况下，结果完全是胡说八道:

In the case at hand the result is utter nonsense:

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

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

While you seem to expect that the second instance of id would reference the function parameter, it does not. The column name comes first within the scope of an SQL statement, the second instance references the column. resulting in an expression that is always true except for NULL values in id. Consequently, you would update all rows, which could lead to catastrophic loss of data. What's worse, you might not even realize it until later, because the SQL function will return one arbitrary row as defined by the RETURNING clause of the function (returns one row, not a set of rows).

_{在这种特殊情况下，您会得到幸运"，因为您还拥有value1 = value1，它会使用其预先存在的值覆盖该列，从而以非常昂贵的方式有效地执行了任何操作(除非触发做点什么).您可能会困惑于得到一个任意行，结果中的value1保持不变.}

_{In this particular case, you would get "lucky", because you also have value1 = value1, which overwrites the column with its pre-existing value, effectively doing .. nothing in a very expensive way (unless triggers do something). You may be puzzled to get an arbitrary row with an unchanged value1 as result.}

所以，不要.

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

Avoid potential naming conflicts like this unless you know exactly what you are doing (which obviously isn't the case). One convention I like is to prepend an underscore for parameter and variable names in functions, while column names never start with an underscore. In many cases you can just use positional references to be unambiguous: $1, $2, ..., but that sidesteps only one half of the issue. Any method is good as long as you avoid naming conflicts. I suggest:

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

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

Also note that this returns the actual column value in value1 after the UPDATE, which may or may not be the same as your input parameter _value1. There could be database rules or triggers interfering ...

这篇关于Postgres函数返回一行作为JSON值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！