PostgreSQL13如何高效获取查询结果及总数避免重复执行？

你的现有方案通过UNION ALL将数据行与统计行合并，会导致结果集结构不一致，后续处理易出问题。以下是PostgreSQL中更合理的解决方案，均能保证原函数selectItemsByRootTag只执行一次：

方案1：临时表+多结果集返回（适合支持多结果集的客户端） #

通过临时表缓存原函数的执行结果，然后分别返回过滤后的数据和统计条数。逻辑清晰，客户端可分别处理两个独立的结果集。

CREATE OR REPLACE FUNCTION get_filtered_items_with_count(
    in tag_name VARCHAR(50),
    in max_distance INTEGER
)
AS $$
BEGIN
    -- 创建临时表缓存原函数结果，会话结束自动销毁
    CREATE TEMP TABLE temp_items ON COMMIT DROP AS
    SELECT * FROM selectItemsByRootTag(tag_name);

    -- 返回过滤后的数据
    RETURN QUERY
    SELECT id, name, description, distance
    FROM temp_items
    WHERE distance < max_distance;

    -- 返回原函数结果的总条数（如需过滤后的总条数，添加WHERE distance < max_distance即可）
    RETURN QUERY
    SELECT count(*)::BIGINT AS id, NULL::VARCHAR(50) AS name, NULL::TEXT AS description, NULL::INTEGER AS distance
    FROM temp_items;
END;
$$ LANGUAGE plpgsql;

调用示例：

SELECT * FROM get_filtered_items_with_count('your_tag_name', 400);

执行后客户端会收到两个结果集：第一个是符合条件的数据行，第二个是统计条数。

方案2：返回复合类型结果（适合单结果集场景） #

定义复合类型，将过滤后的数据数组、原结果总条数、过滤后条数封装在一个结果行中，适合需要一次性获取所有信息的场景。

-- 定义复合类型，复用原函数的行类型
CREATE TYPE items_stat_result AS (
    filtered_data selectItemsByRootTag%ROWTYPE[],
    total_count BIGINT,
    filtered_count BIGINT
);

CREATE OR REPLACE FUNCTION get_filtered_items_with_stat(
    in tag_name VARCHAR(50),
    in max_distance INTEGER
)
RETURNS items_stat_result
AS $$
DECLARE
    all_data selectItemsByRootTag%ROWTYPE[];
    total BIGINT;
    filtered BIGINT;
BEGIN
    -- 一次性获取原函数结果并转为数组，同时统计总条数
    SELECT array_agg(row), count(*) INTO all_data, total
    FROM selectItemsByRootTag(tag_name) row;

    -- 统计过滤后的条数
    SELECT count(*) INTO filtered
    FROM unnest(all_data) item
    WHERE item.distance < max_distance;

    -- 返回包含数据和统计的复合结果
    RETURN (
        ARRAY(SELECT item FROM unnest(all_data) item WHERE item.distance < max_distance),
        total,
        filtered
    )::items_stat_result;
END;
$$ LANGUAGE plpgsql;

调用示例：

SELECT * FROM get_filtered_items_with_stat('your_tag_name', 400);

结果返回一行，其中filtered_data是符合条件的数据行数组，total_count是原函数结果的总条数，filtered_count是过滤后的条数。

方案3：CTE+窗口函数（轻量场景） #

如果只需在过滤后的数据中携带总条数（无需单独统计行），可直接用窗口函数实现，无需编写额外函数，原函数仅执行一次。

获取过滤后数据+原结果总条数 #

WITH func_result AS (
    SELECT *, count(*) OVER() AS total_count
    FROM selectItemsByRootTag('your_tag_name')
)
SELECT id, name, description, distance, total_count
FROM func_result
WHERE distance < 400;

获取过滤后数据+过滤后条数 #

WITH filtered_result AS (
    SELECT * FROM selectItemsByRootTag('your_tag_name') WHERE distance < 400
)
SELECT *, count(*) OVER() AS filtered_count
FROM filtered_result;

关于“生成器函数”的说明 #

PostgreSQL中返回表的函数（如你的selectItemsByRootTag）本身就具备类似生成器的特性，会按需返回数据行。要避免重复执行函数，核心是缓存函数的执行结果——上述方案分别通过CTE、临时表、数组实现了结果缓存，从而避免重复调用带来的性能损耗。

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