从 PostgreSQL 中函数的返回中删除双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40928325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove double quotes from the return of a function in PostgreSQL
提问by Leo
I have the following function in PostgreSQL
我在 PostgreSQL 中有以下功能
CREATE OR REPLACE FUNCTION public.translatejson(JSONB, TEXT)
RETURNS TEXT
AS
$BODY$
SELECT (->)::TEXT
$BODY$
LANGUAGE sql STABLE;
When I execute it I receive the values surrounded by double quotes. For example:
当我执行它时，我收到双引号包围的值。例如：
SELECT id, translatejson("title", 'en-US') AS "tname" FROM types."FuelTypes";
SELECT id, translatejson("title", 'en-US') AS "tname" FROM types."FuelTypes";
in return I get a table like this
作为回报，我得到了一张这样的桌子
-------------------
| id | tname |
-------------------
| 1 | "gasoline" |
| 2 | "diesel" |
-------------------
The values in the 'title' column are in JSON format: { "en-US":"gasoline", "fr-FR":"essence" }. How I can omit the double quotes to return just the string of the result?
'title' 列中的值采用 JSON 格式：{ "en-US":"gasoline", "fr-FR":"essence" }。如何省略双引号以仅返回结果字符串？
回答by Craig Ringer
The ->operator returns a jsonresult. Casting it to textleaves it in a json reprsentation.
该->运算符返回一个json结果。将其转换text为 json 表示形式。
The ->>operator returns a textresult. Use that instead.
该->>运算符返回一个text结果。改用那个。
test=> SELECT '{"car": "going"}'::jsonb -> 'car';
?column?
----------
"going"
(1 row)
test=> SELECT '{"car": "going"}'::jsonb ->> 'car';
?column?
----------
going
(1 row)
