Функция Postgres, возвращающая строку как значение JSON

Я вижу две основные проблемы:
1. Вы не можете поместить UPDATE в подзапрос вообще. Вы можете решить эту проблему с помощью CTE, изменяющего данные. как Патрик демонстрирует, но это дороже и многословнее, чем необходимо для данного случая.
2. У вас есть потенциально опасный конфликт имен, который еще не решен.

Лучший запрос/функция

На данный момент оставим в стороне оболочку функции SQL (мы к ней еще вернемся). Вы можете использовать простой UPDATE с предложением RETURNING:

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 для инкапсуляции первого шага или приведения к четко определенному типу строки. Подробности:

• Возврат в виде массива объектов JSON в SQL (Postgres )

В Postgres 9.4 просто используйте json_build_object() или json_object():

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

Or:

...
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 id = id
    Returning Value1, Value2;
...
$$ Language SQL;

Хотя вы, кажется, ожидаете, что второй экземпляр id будет ссылаться на параметр функции, это не так. Имя столбца идет первым в рамках оператора SQL, второй экземпляр ссылается на столбец. в результате получается выражение, которое всегда равно true, за исключением значений NULL в id. Следовательно, вам придется обновить все строки, что может привести к катастрофической потере данных. Что еще хуже, вы можете даже не осознавать этого до тех пор, пока функция SQL не вернет одну произвольную строку, как определено предложением RETURNING функции (возвращает одну строку, а не набор строк).

_{В этом конкретном случае вам повезет, потому что у вас также есть value1 = value1, который перезаписывает столбец его ранее существовавшим значением, фактически ничего не делая очень дорогим способом (если только триггеры не делают что-то). Вы можете быть озадачены, получив в результате произвольную строку с неизменным value1.}

Так что не надо.

Избегайте потенциальных конфликтов имен, подобных этому, если вы точно не знаете, что делаете (что, очевидно, не так). Одно соглашение, которое мне нравится, заключается в том, чтобы добавлять подчеркивание к именам параметров и переменных в функциях, в то время как имена столбцов никогда не начинаются с подчеркивания. Во многих случаях вы можете просто использовать позиционные ссылки для однозначности: $1, $2, ..., но это решает только половину проблемы. Любой метод хорош, если вы избегаете конфликтов имен. Я предлагаю:

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;

Также обратите внимание, что это возвращает фактическое значение столбца в value1 после UPDATE, которое может совпадать или не совпадать с вашим входным параметром _value1. Могут быть правила базы данных или триггеры, мешающие...

Вам нужно поместить оператор UPDATE в CTE:

CREATE OR REPLACE FUNCTION ExampleTable_Update (id bigint, value1 text) RETURNS json AS $$
  WITH t(Value2) AS (
    UPDATE ExampleTable
    SET Value1 = $2
    WHERE id = $1
    RETURNING Value2)
  SELECT row_to_json($2, Value2) 
  FROM t;
$$ LANGUAGE sql;

Обратите внимание, что я использую позиционные параметры $1 и $2 для параметров функции. Имена этих параметров совпадают с именами столбцов в таблице, и это, как правило, плохая идея из-за возможных конфликтов разрешения имен; см. ответ Эрвина Брандштеттера для более подробного объяснения.