如何让PostgreSQL存储过程sp_table返回指定格式的JSON结果？

完全可以实现你期望的格式，核心是确保存储过程返回的第二列类型为原生JSON类型（json或jsonb），而非字符串类型（text/varchar）。

问题根源 #

你当前的sp_table可能是将JSON对象转换为字符串返回（比如用::text强制转换），导致结果带有外层双引号；而sp_only_json是直接返回json类型，所以输出符合预期。

具体实现方案 #

改写存储过程，让返回列的类型定义为json，并直接返回JSON构造函数的结果（无需转字符串）：

CREATE OR REPLACE FUNCTION sp_table()
RETURNS TABLE(id integer, data json) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    t.id,
    -- 直接构造JSON对象并返回json类型
    json_build_object('cola', t.cola, 'colb', t.colb) AS data
  FROM your_table t; -- 替换为你的实际表名
END;
$$ LANGUAGE plpgsql;

验证效果 #

调用存储过程时：

SELECT * FROM sp_table();

会得到你期望的输出格式：

id |         data
----+----------------------
  1 | {"cola":1,"colb":"a"}
  2 | {"cola":2,"colb":"b"}

补充说明 #

如果你的场景需要更复杂的JSON结构，也可以使用row_to_json()函数直接将行数据转为JSON：

SELECT t.id, row_to_json(t.*) AS data FROM your_table t;

只要确保返回列的类型是json/jsonb，就不会出现外层双引号的问题。

内容的提问来源于stack exchange，提问作者Pvn