PostgreSQL自定义函数仅返回列首值问题求助

问题根源 #

你写的无参数函数内部通过SELECT INTO将查询结果赋值给单个text变量，这种写法只会提取查询结果的第一行——因为单个变量只能存储一个值，这就是单独执行SELECT能得到所有行，但函数仅返回首个值的原因。

正确解决方案：带参数的标量函数 #

你需要的是一个处理单个数值、返回格式化字符串的标量函数，这样可以直接在查询中作为列使用，传入每行的amount值。重写后的函数如下：

CREATE OR REPLACE FUNCTION rental_amount(p_amount numeric)
RETURNS text AS $dollar_amount$
BEGIN
  RETURN TO_CHAR(p_amount, 'L9D99');
END;
$dollar_amount$ LANGUAGE plpgsql;

简化版本（SQL函数更高效） #

如果不需要复杂逻辑，用SQL函数更简洁：

CREATE OR REPLACE FUNCTION rental_amount(p_amount numeric)
RETURNS text AS
$$
  SELECT TO_CHAR(p_amount, 'L9D99');
$$ LANGUAGE sql;

使用方式 #

现在可以直接把这个函数当作列嵌入任意查询，比如：

SELECT 
  title,
  rental_amount(amount) AS formatted_amount
FROM detail_report
ORDER BY title;

这样每一行的amount都会被格式化为带美元符号的字符串，完全满足嵌入其他查询的需求。

为什么返回表不符合需求？ #

返回表类型的函数必须用SELECT * FROM rental_amount()的方式调用，无法直接作为列嵌入到其他查询的SELECT列表中；而带参数的标量函数可以像内置函数一样，接收每行的字段值并返回对应结果，完美适配你的场景。

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