Django查询报错：WHERE子句不允许使用返回集合的函数

问题根源 #

你用的REGEXP_MATCHES是PostgreSQL里的返回集合（set-returning）函数，哪怕你取了[1]拿到第一个匹配结果，PostgreSQL在WHERE子句里依然会认定它是返回多行的函数，因此抛出set-returning functions are not allowed in WHERE错误。而SELECT子句对这类函数的限制宽松，所以只做注解的时候能正常运行。

解决方案：改用REGEXP_REPLACE处理邮编 #

不需要用匹配函数提取数字，直接用REGEXP_REPLACE把所有非数字字符替换为空，得到纯数字字符串后再转成整数。这样得到的是标量值，完全符合WHERE子句的要求。

修改你的数据库函数：

class NumericPostcode(models.Func):
    """
    Removes non-numeric characters from field and converts to integer
    """
    function = "REGEXP_REPLACE"
    template = "CAST( %(function)s(%(expressions)s, '[^0-9]', '', 'g') AS INTEGER )"
    output_field = models.IntegerField()

代码说明 #

• '[^0-9]'：匹配所有非数字字符
• ''：把匹配到的非数字字符替换成空字符串
• 'g'：全局替换（默认只替换第一个匹配项）

验证效果 #

修改后，生成的SQL WHERE子句会变成类似这样：

WHERE (
    cast( regexp_replace("order_shippingaddress"."postcode", '[^0-9]', '', 'g') AS INTEGER ) BETWEEN 1000 AND 1789
    OR cast( regexp_replace("order_shippingaddress"."postcode", '[^0-9]', '', 'g') AS INTEGER ) BETWEEN 1798 AND 2099
    -- ...其他范围条件
)

这里的REGEXP_REPLACE是标量函数，不会触发返回集合的错误，同时能正确提取邮编里的数字并完成范围过滤。

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