PostgreSQL 11如何将视图过滤条件直接作为函数参数传入使用

结论 #

这个需求在PostgreSQL 11中可以实现，核心是利用PostgreSQL的谓词下推特性和横向连接，将视图上的参数过滤条件直接传递给底层函数作为入参，不会对函数返回的结果集做二次过滤。

前置调整：修正函数定义 #

你现有函数的两个属性会阻碍功能实现，需要先调整：

• 返回值标记为UNKNOWN不符合表函数的要求，需要明确指定返回的表结构
• NOT DETERMINISTIC对应函数的VOLATILE属性，会阻止查询规划器做谓词下推，需要修改为STABLE（同一个事务内相同入参返回结果一致，符合你这个函数的特性）

修正后的函数代码：

CREATE OR REPLACE FUNCTION "test_func_params"(
    "_ts_from" TIMESTAMP,
    "_ts_to" TIMESTAMP
)
RETURNS TABLE(date_from TIMESTAMP, date_to TIMESTAMP, name TEXT)
LANGUAGE SQL
STABLE
CONTAINS SQL
SQL SECURITY DEFINER
AS $$ 
SELECT date_from, date_to, name FROM master_table where date_from <= $1 and date_to >= $2;
$$;

视图定义 #

通过横向连接+参数占位列的方式构建视图，暴露参数列用于接收过滤条件：

CREATE VIEW "view_test" AS
SELECT
  f.*
FROM
  -- 虚表作为参数占位，提供过滤用的参数列
  (SELECT NULL::TIMESTAMP AS _ts_from, NULL::TIMESTAMP AS _ts_to) p,
  -- 横向连接调用函数，入参绑定占位列
  LATERAL test_func_params(p._ts_from, p._ts_to) f;

如果你需要在查询结果中看到传入的参数，也可以把p._ts_from, p._ts_to加到SELECT的返回列中。

查询方式 #

直接对视图加过滤条件即可，和你期望的用法一致：

SELECT * FROM view_test 
WHERE _ts_from = '2024-01-01 00:00:00' 
  AND _ts_to = '2024-01-31 23:59:59'
  AND name = 'foo';

注意：SQL中字符串常量需要用单引号，双引号仅用于转义标识符（如表名、列名）

生效验证 #

你可以通过EXPLAIN语句查看执行计划，确认过滤条件已经下推到函数内部，并且触发了分区表的分区裁剪，没有全表扫描函数返回的所有结果，即代表配置生效。

注意事项 #

• 对_ts_from、_ts_to的过滤条件必须是等值条件，才能触发下推
• 函数不能包含副作用操作，必须是STABLE或IMMUTABLE属性

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