PostgreSQL只读实例如何封装复用SELECT块实现多表同逻辑查询

核心限制说明 #

只读PostgreSQL实例不允许创建持久化的自定义函数/存储过程，因为这类对象的创建需要写入系统表，违反只读权限约束，因此直接使用永久函数的方案不可行。

两种可行的封装方案 #

方案1：使用临时函数（最贴近封装调用需求） #

临时函数仅在当前会话生效，数据存储在会话级临时schema pg_temp中，不会写入持久化系统表，只读实例完全支持。

示例代码：

1. 先创建临时函数，入参为要查询的表名，返回结构和你需要的查询结果一致：

CREATE OR REPLACE FUNCTION pg_temp.query_target_table(tablename text)
RETURNS TABLE(
    day date,
    nb_1 bigint,
    clients_1 text -- 请根据你实际clients字段的类型调整
)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT 
      DATE(ts) AS day,
      COUNT(*) AS nb_1,
      clients AS clients_1
    FROM %I
    WHERE location IN (''USA'', ''SPAIN'')
    GROUP BY day, clients_1',
    tablename
  );
END;
$$;

注意：你原SQL中COUNT(*) AS nb_1后面缺少逗号，会触发语法错误，上述代码已补全。

2. 调用时直接传入表名即可，无需重复写查询逻辑：

-- 查询table_1
SELECT * FROM pg_temp.query_target_table('table_1');
-- 查询table_2
SELECT * FROM pg_temp.query_target_table('table_2');
-- 需要合并多表结果的话可以拼接UNION ALL
SELECT * FROM pg_temp.query_target_table('table_1')
UNION ALL
SELECT * FROM pg_temp.query_target_table('table_2');

方案2：纯查询逻辑封装（无需创建任何对象） #

如果你所有table_1到table_N都是在同一个查询的WITH子句中定义的，可以先将所有表合并后统一执行查询逻辑，完全不需要创建额外对象，100%适配只读实例：

WITH table_1 AS (
.....
),
table_2 AS (
.....
),
-- 此处省略其他table_N的定义
all_tables AS (
  -- 统一合并所有要查询的表，新增table_source字段区分来源
  SELECT 'table_1' AS table_source, t.* FROM table_1 t
  UNION ALL
  SELECT 'table_2' AS table_source, t.* FROM table_2 t
  UNION ALL
  SELECT 'table_N' AS table_source, t.* FROM table_N t
)
-- 统一执行相同的查询逻辑
SELECT
  table_source,
  DATE(ts) AS day,
  COUNT(*) AS nb_1,
  clients AS clients_1
FROM all_tables
WHERE location IN ('USA', 'SPAIN')
GROUP BY table_source, day, clients_1;

函数使用的结论 #

• 永久函数：不合适，只读实例不允许创建持久化对象，执行会报错
• 临时函数：完全合适，符合只读实例权限约束，可实现需求

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