如何编写Psql函数实现返回仅含dep_people列的单行字符串结果

你可以使用PostgreSQL内置的string_agg聚合函数完成多行字符串拼接，再封装为符合要求的自定义函数即可。

基础拼接逻辑验证 #

先执行以下查询验证拼接结果是否符合要求，入参替换为你要查询的organization_id：

SELECT string_agg(
    initcap(replace(department, ' ', '_')) || ':' || no_people::text,
    ', '
    ORDER BY department
) AS dep_people
FROM test_sch.apollo_org_job_function
WHERE organization_id = '1a';

参数说明：

• replace(department, ' ', '_')：将部门名称中的空格替换为下划线，匹配示例中的human_resources格式
• initcap()：将部门名称首字母大写，匹配示例中的Engineering格式，不需要可以直接删除该函数
• string_agg(..., ', ')：将所有行的拼接结果用, 连接为单个字符串，分隔符可以根据需求自行调整
• 末尾的ORDER BY department可以指定拼接时的部门排序规则，不需要可以直接删除

封装为自定义函数 #

直接在test_sch schema下创建函数：

CREATE OR REPLACE FUNCTION test_sch.return_dep_people(p_org_id text)
RETURNS TABLE (dep_people text)
LANGUAGE sql STABLE
AS $$
SELECT string_agg(
    initcap(replace(department, ' ', '_')) || ':' || no_people::text,
    ', '
    ORDER BY department
) AS dep_people
FROM test_sch.apollo_org_job_function
WHERE organization_id = p_org_id;
$$;

函数说明：

• 入参p_org_id接收传入的organization_id值
• RETURNS TABLE (dep_people text)定义返回结果的列名和类型，完全匹配要求的调用格式
• STABLE标识表示相同入参多次调用返回结果一致，用于PostgreSQL优化查询性能
• 简单查询类函数使用LANGUAGE sql编写比plpgsql更简洁高效

调用验证 #

直接按要求的方式调用即可：

select * from test_sch.return_dep_people('1a') as f(dep_people text);

返回结果就是符合要求的1行1列的拼接字符串。

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