PostgreSQL函数：首次查询无结果返回默认行，如何正确使用WITH AS

你的问题出在PL/pgSQL中CTE（WITH AS定义的临时结果集）的作用域限制：CTE仅在它所属的单个SQL语句内有效，不能跨PL/pgSQL的控制结构（比如IF EXISTS）直接引用。而且EXISTS()需要传入子查询，不是CTE的名称，这也是你代码报错的核心原因。

下面给你两种优化方案，既避免重复查询，又正确使用WITH AS：

方案1：用纯SQL函数实现（推荐，更高效） #

这种方式不需要PL/pgSQL的条件判断，直接用CTE结合UNION ALL和NOT EXISTS完成逻辑，PostgreSQL可以对整个查询做优化，避免重复扫描表，代码也更简洁：

CREATE OR REPLACE FUNCTION myTestProcedure(namevalue character varying) 
RETURNS TABLE(id integer, name character varying, isdefault boolean) 
LANGUAGE sql AS $function$
WITH match_result AS (
    -- 先定义匹配namevalue的结果集
    SELECT id, name, isdefault 
    FROM Domain 
    WHERE lower(name) LIKE namevalue
)
-- 优先返回匹配结果，如果没有匹配，再返回默认记录
SELECT * FROM match_result
UNION ALL
SELECT id, name, isdefault 
FROM Domain 
WHERE isdefault = true
AND NOT EXISTS (SELECT 1 FROM match_result);
$function$;

方案2：在PL/pgSQL中正确使用CTE #

如果一定要用PL/pgSQL，你需要把CTE的结果逻辑整合到单个RETURN QUERY的SQL语句中，或者先把CTE的结果计数保存到变量，再做判断：

方式A：单SQL语句内完成逻辑 #

CREATE OR REPLACE FUNCTION myTestProcedure(namevalue character varying) 
RETURNS TABLE(id integer, name character varying, isdefault boolean) 
LANGUAGE plpgsql AS $function$
BEGIN
    RETURN QUERY
    WITH match_result AS (
        SELECT id, name, isdefault 
        FROM Domain 
        WHERE lower(name) LIKE namevalue
    )
    SELECT * FROM match_result
    UNION ALL
    SELECT id, name, isdefault FROM Domain WHERE isdefault = true
    AND NOT EXISTS (SELECT 1 FROM match_result);
END $function$;

方式B：用变量保存匹配计数 #

CREATE OR REPLACE FUNCTION myTestProcedure(namevalue character varying) 
RETURNS TABLE(id integer, name character varying, isdefault boolean) 
LANGUAGE plpgsql AS $function$
DECLARE
    match_count integer;
BEGIN
    -- 先通过CTE统计匹配记录数
    WITH match_result AS (
        SELECT id, name, isdefault 
        FROM Domain 
        WHERE lower(name) LIKE namevalue
    )
    SELECT COUNT(*) INTO match_count FROM match_result;
    
    -- 根据计数判断返回结果
    IF match_count > 0 THEN
        RETURN QUERY SELECT id, name, isdefault FROM Domain WHERE lower(name) LIKE namevalue;
    ELSE
        RETURN QUERY SELECT id, name, isdefault FROM Domain WHERE isdefault = true;
    END IF;
END $function$;

关键说明 #

• 避免在PL/pgSQL的控制结构（如IF）中直接引用CTE，因为CTE的作用域仅限于定义它的那一条SQL语句。
• 纯SQL函数的方案性能更优，因为PostgreSQL可以对整个查询做优化，减少不必要的表扫描。

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