错误:返回的类型未知，位于序号位置2，但查询需要文本

Question

我在PostgreSQL中创建了一个SQL函数，它返回了一个带有文本值的表，它抛出了错误

NOTICE:  function func() does not exist, skipping
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type unknown at ordinal position 2, but query expects text.
SQL state: 42804

功能定义：

CREATE OR REPLACE function func(a)
RETURNS TABLE(num int, display text) AS $$
SELECT CASE
WHEN (num >= 10) THEN (10, 'MEDIUM')
WHEN (num >= 20) THEN (20, 'HIGH')
ELSE (0, 'LOW')
END
$$
language sql stable strict;

Answer 1

您可以尝试使用双冒号运算符将字符串文本显式转换为文本：

CREATE OR REPLACE function func(a)
RETURNS TABLE(num int, display text) AS $$
SELECT CASE
WHEN (num >= 10) THEN (10, 'MEDIUM'::text)
WHEN (num >= 20) THEN (20, 'HIGH'::text)
ELSE (0, 'LOW'::text)
END
$$
language sql stable strict;