PostgreSQL中如何使用表列值作为函数参数生成汇总统计结果

我在PostgreSQL中通过如下SQL创建了summaryTable表：

CREATE TABLE summaryTable AS (
    SELECT d.name, d.store_id, d.rental_id
    FROM detailedTable AS d
    GROUP BY d.store_id, d.name
    ORDER BY d.name ASC
) 

我之前写了一个入参为电影类型、返回该类型出现次数的自定义函数，想把函数计算结果作为新列加入summaryTable，入参取表中 d.name 字段的值。
需要实现的逻辑：逐行取首列的类型名称作为函数入参，统计对应类型的出现次数，最终返回包含「类型名称、1号门店该类型出现次数、2号门店该类型出现次数」的结果表。

当前已写的函数仅支持统计1号门店的数值，无法满足需求，代码如下：

CREATE OR REPLACE FUNCTION countGenre (genre varchar)
RETURNS integer AS $totals$
DECLARE totals integer;
BEGIN
    SELECT COUNT(d.name) INTO totals
    FROM detailedTable AS d
    WHERE d.name = genre AND d.store_id = 1
    GROUP BY d.name;
    RETURN totals;
    END; $totals$
LANGUAGE plpgsql;

推荐方案：直接条件聚合（性能最优） #

不需要自定义函数逐行调用，直接通过条件聚合一次查询就能得到目标结果，数据量较大时性能远高于逐行函数调用：

SELECT
    name AS genre_name,
    COUNT(*) FILTER (WHERE store_id = 1) AS store1_count,
    COUNT(*) FILTER (WHERE store_id = 2) AS store2_count
FROM detailedTable
GROUP BY name
ORDER BY name ASC;

PostgreSQL原生支持FILTER子句做条件计数，比CASE WHEN写法更简洁易读。

方案2：改造自定义函数适配需求 #

如果必须使用自定义函数实现，可以把函数改造为返回两个门店计数的出参，避免多次扫表：

CREATE OR REPLACE FUNCTION countGenre (
    IN genre varchar,
    OUT store1_total integer,
    OUT store2_total integer
) AS $$
BEGIN
    SELECT
        COUNT(*) FILTER (WHERE store_id = 1),
        COUNT(*) FILTER (WHERE store_id = 2)
    INTO store1_total, store2_total
    FROM detailedTable
    WHERE name = genre;
END;
$$ LANGUAGE plpgsql;

调用函数生成目标结果的SQL如下：

SELECT DISTINCT
    name AS genre_name,
    (countGenre(name)).store1_total AS store1_count,
    (countGenre(name)).store2_total AS store2_count
FROM summaryTable
ORDER BY name ASC;

注意：如果后续门店数量扩展，不建议继续用函数加固定出参的写法，直接用条件聚合的扩展性更好。

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