PostgreSQL技术问询：获取查询行数及函数返回无限制匹配总行数

嘿，很高兴帮你搞定这两个PostgreSQL问题，咱们一个个来拆解：

问题1：如何获取最近执行的SELECT查询返回的行数 #

这个需求得分场景来看，不同场景下的实现方式不一样：

• 在PL/pgSQL函数/存储过程中
  执行完SELECT后，用GET DIAGNOSTICS命令就能直接拿到返回行数，示例代码如下：

  DECLARE
      row_count integer;
  BEGIN
      -- 执行你的SELECT查询
      SELECT * FROM your_table WHERE your_condition;
      -- 获取返回行数
      GET DIAGNOSTICS row_count = ROW_COUNT;
      RAISE NOTICE '本次查询返回了 % 行数据', row_count;
  END;
  

  这里的ROW_COUNT会记录最近一次SQL命令影响的行数，对SELECT来说就是返回的结果行数。

• 在psql交互式命令行中
  执行查询后，psql会自动在输出底部显示行数（比如(5 rows)）。如果要把行数存成变量复用，可以用内置的:ROW_COUNT变量：

  -- 先执行你的查询
  SELECT * FROM your_table WHERE your_condition;
  -- 查看行数
  SELECT :ROW_COUNT;
  -- 或者把行数存入自定义变量
  \set total_rows :ROW_COUNT
  SELECT :total_rows;
  

• 查看历史/其他会话的查询行数
  如果你想找之前执行的或者其他会话的SELECT查询行数，可以查询pg_stat_user_queries视图（需要超级用户权限），其中的rows字段就是对应查询返回的行数：

  SELECT query, rows, query_start
  FROM pg_stat_user_queries
  WHERE query LIKE 'SELECT%'
  ORDER BY query_start DESC
  LIMIT 1; -- 获取最近的一条SELECT查询
  

问题2：让分页函数同时返回数据和总行数 #

你的public.get_notifications函数要实现分页+返回符合条件的总行数，这里有几种适合初学者的实用方案：

方案1：用CTE同时计算总行数和分页数据 #

这种方式最直观，先通过CTE算出所有符合条件的总行数，再查询分页数据，最后把两者组合返回：

CREATE OR REPLACE FUNCTION public.get_notifications(
    search_text character,
    page_no integer,
    count integer
) RETURNS TABLE(total_rows integer, id integer, content text, created_at timestamp) -- 替换成你的表字段
LANGUAGE plpgsql
AS $$
DECLARE
    offset_val integer;
BEGIN
    offset_val := (page_no - 1) * count;
    
    WITH total AS (
        -- 先计算符合条件的总行数
        SELECT COUNT(*) AS total
        FROM your_notification_table -- 替换成你的表名
        WHERE your_search_column LIKE '%' || search_text || '%' -- 替换成你的WHERE条件
    )
    -- 关联总行数和分页数据，返回结果
    SELECT total.total, n.id, n.content, n.created_at
    FROM total, your_notification_table n
    WHERE n.your_search_column LIKE '%' || search_text || '%'
    LIMIT count OFFSET offset_val;
END;
$$;

调用函数后，每一行结果都会携带相同的total_rows值，你在应用端取第一行的该值即可得到总行数。

方案2：用窗口函数COUNT(*) OVER() #

这种方法只需要一次查询，效率更高，PostgreSQL优化器会自动避免重复扫描表：

CREATE OR REPLACE FUNCTION public.get_notifications(
    search_text character,
    page_no integer,
    count integer
) RETURNS TABLE(total_rows integer, id integer, content text, created_at timestamp)
LANGUAGE plpgsql
AS $$
DECLARE
    offset_val integer;
BEGIN
    offset_val := (page_no - 1) * count;
    
    -- 用窗口函数在查询数据时同时计算总行数
    SELECT COUNT(*) OVER(), id, content, created_at
    FROM your_notification_table
    WHERE your_search_column LIKE '%' || search_text || '%'
    LIMIT count OFFSET offset_val;
END;
$$;

和方案1类似，每一行都会返回总行数，应用端取第一行的值即可。

方案3：用OUT参数单独返回总行数 #

如果你希望总行数和分页数据分开获取，可以用OUT参数：

CREATE OR REPLACE FUNCTION public.get_notifications(
    search_text character,
    page_no integer,
    count integer,
    OUT total_rows integer -- 新增OUT参数返回总行数
) RETURNS SETOF your_notification_table -- 直接返回表类型的结果集
LANGUAGE plpgsql
AS $$
DECLARE
    offset_val integer;
BEGIN
    -- 先计算总行数
    SELECT COUNT(*) INTO total_rows
    FROM your_notification_table
    WHERE your_search_column LIKE '%' || search_text || '%';
    
    offset_val := (page_no - 1) * count;
    
    -- 返回分页数据
    RETURN QUERY
        SELECT *
        FROM your_notification_table
        WHERE your_search_column LIKE '%' || search_text || '%'
        LIMIT count OFFSET offset_val;
END;
$$;

调用函数时，你会同时得到作为输出参数的总行数，以及分页的结果集。

小提示：如果你的表数据量很大，COUNT(*)可能会变慢，这时候可以考虑用pg_stat_user_tables里的n_live_tup做近似计数；另外，LIKE '%search_text%'的性能较差，建议给搜索字段创建全文索引（用tsvector/tsquery）来优化。

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