SELECT查询存在性校验问题：getPattern无合规结果时的优化方案

问题背景 #

我有个自定义函数getPattern(integer)，它返回的结果里混着两种模式：一种是带.的（比如'....3...'，能匹配像comp3333这类字符串），另一种是完全不带.的、符合^[A-Z]{4}[0-9]{4}$格式的纯4大写字母加4数字的模式。我只想筛选出后者，于是写了下面的SQL：

SELECT pattern as pn 
FROM getPattern($1) 
WHERE EXISTS (
    SELECT pattern as pr 
    FROM getPattern($1) 
    WHERE pr ~* '^[A-Z]{4}[0-9]{4}$'
) 
AND pn ~* '^[A-Z]{4}[0-9]{4}$'

但麻烦的是：当getPattern($1)返回的结果里完全没有不带.的模式时，这个SQL会报错，求个简洁的解决办法。

解决思路 #

1. 用CTE预存函数结果（推荐，还能提升性能） #

你的原SQL重复调用了两次getPattern($1)，不仅浪费性能，还可能因为函数的特殊逻辑（比如无结果时抛出异常）导致报错。我们可以先把函数的结果一次性存到临时公共表表达式（CTE）里，再基于这个结果筛选，这样既只调用一次函数，又能保证没有匹配行时返回空结果集而非报错：

WITH pattern_results AS (
    SELECT pattern FROM getPattern($1)
)
SELECT pattern AS pn
FROM pattern_results
WHERE pattern ~* '^[A-Z]{4}[0-9]{4}$';

2. 直接去掉多余的EXISTS条件 #

其实你原SQL里的EXISTS子查询完全是画蛇添足——外层已经用pn ~* '^[A-Z]{4}[0-9]{4}$'筛选了符合要求的行，不管有没有符合条件的结果，这个简化后的SQL都会正常返回（有匹配就输出对应行，没有就返回空），不会报错：

SELECT pattern AS pn
FROM getPattern($1)
WHERE pattern ~* '^[A-Z]{4}[0-9]{4}$';

为什么原SQL会报错？ #

大概率是因为你重复调用了getPattern($1)，如果这个函数在无返回结果时会触发自定义异常，或者数据库对重复调用的函数有特殊处理逻辑，那么CTE的方式就能彻底避免这个问题——只调用一次函数，后续操作都基于预存的结果集，不管有没有匹配行，都不会触发异常。如果是你把“返回空结果”当成了报错，那第二个简化方案就完全能解决你的问题。

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