创建返回多行拼接结果的PostgreSQL函数遇问题

问题根源 #

1. 你的原函数返回类型定义为varchar(90)，这意味着它只能返回单个字符串值，执行SELECT ... INTO staff_fname时，只会取查询结果的第一行赋值给变量，最终仅返回这一行。
2. 注意：PostgreSQL中SELECT ... INTO有两种完全不同的用法：
   • 在普通SQL语句中，SELECT ... INTO 表名是创建新表并插入查询结果（你最初的单独SELECT语句就是这个逻辑）；
   • 在PL/pgSQL函数中，SELECT ... INTO 变量名是将查询结果赋值给单个变量，仅当查询返回单行时有效。

正确实现：返回所有拼接后的姓名结果集 #

如果需要函数返回所有拼接后的姓名，需将函数返回类型改为SETOF varchar(90)，并使用RETURN QUERY直接返回查询结果，无需声明单个变量：

CREATE FUNCTION connames()
RETURNS SETOF varchar(90)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT CONCAT(first_name, ' ', last_name)
    FROM staff;
END;
$$;

调用函数时，用以下语句获取所有结果：

SELECT * FROM connames();

额外：如果需要同时将结果插入staff_fname表 #

如果你的需求是让函数完成插入数据到staff_fname表+返回所有拼接结果，可以修改函数如下：

CREATE FUNCTION connames()
RETURNS SETOF varchar(90)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 若staff_fname表已存在，执行插入
    INSERT INTO staff_fname(full_name)
    SELECT CONCAT(first_name, ' ', last_name)
    FROM staff;

    -- 返回所有拼接结果
    RETURN QUERY
    SELECT CONCAT(first_name, ' ', last_name)
    FROM staff;
END;
$$;

注意：如果staff_fname表还未创建，可先用你的初始SELECT语句创建：

SELECT CONCAT(first_name, ' ', last_name) AS full_name INTO staff_fname FROM staff;

你之前尝试LOOP/RETURNS SETOF未成功的可能原因 #

如果之前用LOOP，需要显式收集每一行结果并返回，写法示例：

CREATE FUNCTION connames()
RETURNS SETOF varchar(90)
LANGUAGE plpgsql
AS $$
DECLARE
    full_name varchar(90);
BEGIN
    FOR full_name IN
        SELECT CONCAT(first_name, ' ', last_name) FROM staff
    LOOP
        RETURN NEXT full_name;
    END LOOP;
END;
$$;

这种写法也能生效，但RETURN QUERY更简洁高效。

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