PostgreSQL函数返回查询字符串报错及拼接逻辑疑问

第一个报错的原因与修复 #

你第一个函数里的SELECT语句写法不符合PL/pgSQL规范：PL/pgSQL中如果要将查询结果赋值给变量，必须使用SELECT ... INTO 变量的语法，而不是SELECT 变量 = ...这种写法。你原来的语句只是计算了一个表达式，但没有指定结果的存储目标，因此PostgreSQL抛出“查询结果没有目标”的错误。

多行结果拼接的正确方式 #

你修改后的SELECT ... INTO pram写法只能获取查询结果的第一行，无法自动拼接所有行的内容。要实现多行结果追加到同一个变量，有两种实用方法：

方法1：FOR循环逐行拼接 #

通过遍历结果集，把每行的片段追加到变量中：

CREATE OR REPLACE FUNCTION dbo.fnRepID (pram_ID BIGINT)
RETURNS varchar AS $$
DECLARE 
    pram varchar := '';
    row_fragment varchar;
BEGIN
    -- 遍历过滤后的结果集（记得关联传入的pram_ID参数）
    FOR row_fragment IN
        SELECT (CASE COALESCE(a.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END)
            || (CASE COALESCE(b.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END)
            || f.NAME || ';'
        FROM dbo.ClassRelationship a
            LEFT JOIN dbo.ClassRelationship b ON a.ParentClassID = b.ClassID AND b.Type = 2 AND a.Type = 1
        WHERE a.ClassID = pram_ID -- 根据传入的ID过滤，按需调整条件
    LOOP
        pram := pram || row_fragment;
    END LOOP;
 
    RETURN pram;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION dbo.fnRepID (BIGINT) TO "postgres";

方法2：使用STRING_AGG聚合函数（更高效，推荐） #

利用PostgreSQL内置的字符串聚合函数，一次性完成所有行的拼接，无需循环：

CREATE OR REPLACE FUNCTION dbo.fnRepID (pram_ID BIGINT)
RETURNS varchar AS $$
BEGIN
    RETURN (
        SELECT STRING_AGG(
            (CASE COALESCE(a.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END)
            || (CASE COALESCE(b.Name, '') WHEN '' THEN '' ELSE b.Name || ' - ' END)
            || f.NAME || ';',
            '' -- 拼接分隔符，这里用空字符串直接追加
        )
        FROM dbo.ClassRelationship a
            LEFT JOIN dbo.ClassRelationship b ON a.ParentClassID = b.ClassID AND b.Type = 2 AND a.Type = 1
        WHERE a.ClassID = pram_ID -- 关联传入的参数，按需调整
    );
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION dbo.fnRepID (BIGINT) TO "postgres";

额外注意事项 #

1. 你的函数定义了参数pram_ID，但原查询未使用该参数，会导致返回所有符合JOIN条件的行结果，记得添加WHERE条件关联参数。
2. 第二个CASE语句重复使用b.Name || ' - '，可能是笔误，可检查逻辑是否正确（比如是否应为a.Name || ' - '）。
3. 要获取函数返回值，直接执行SELECT dbo.fnRepID(0);即可，DO块仅用于执行无返回值的PL/pgSQL代码，无法获取返回结果。

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