PostgreSQL 9.6中如何用单语句批量查询多表用户操作记录？

我需要审计应用数据库中特定用户的操作活动，多数重要表均包含以下元数据字段：

• id
• date_last_edited
• last_edited_by

我已实现原型查询：

with "params" as (
    select
        'DeethJ' as user,
        '2022-09-01' as from_date,
        '2022-09-30' as to_date
)

select 'address' as "table", id, date_last_edited, last_edited_by
from address
join params
    on last_edited_by = params.user
    and date_last_edited between params.from_date and params.to_date

union

select 'person' as "table", id, date_last_edited, last_edited_by
from person
join params
    on last_edited_by = params.user
    and date_last_edited between params.from_date and params.to_date

union

select 'person_person_relationship' as "table", id, date_last_edited, last_edited_by
from person_person_relationship
join params
    on last_edited_by = params.user
    and date_last_edited between params.from_date and params.to_date

请问是否存在一种方式，只需提供表名列表即可对每个表执行相同查询，类似union_agg函数的功能？

约束条件：

• 数据库为PostgreSQL 9.6
• 仅能在SAP BusinessObjects的“Freehand SQL数据源”中使用单语句，无法创建函数或存储过程
• 目前考虑用Excel生成查询语句，但希望有更简便的方法

在PostgreSQL 9.6的限制下，你可以通过动态SQL拼接+临时表的方式实现，无需手动写重复的UNION语句，只需指定表名列表即可：

方法1：直接执行动态查询并返回结果 #

WITH params AS (
    SELECT
        'DeethJ' AS username,
        '2022-09-01'::date AS from_date,
        '2022-09-30'::date AS to_date
),
target_tables AS (
    -- 在这里指定需要审计的表名列表，新增/删除表只需修改此数组
    SELECT unnest(ARRAY[
        'address',
        'person',
        'person_person_relationship'
    ]) AS table_name
),
query_parts AS (
    SELECT
        format(
            $$SELECT '%s' AS "table", id, date_last_edited, last_edited_by FROM %s
              JOIN params ON last_edited_by = params.username
              AND date_last_edited BETWEEN params.from_date AND params.to_date$$,
            table_name, table_name
        ) AS sql_part
    FROM target_tables
),
full_query AS (
    SELECT string_agg(sql_part, ' UNION ') AS combined_sql
    FROM query_parts
)
-- 执行动态查询并将结果存入临时表，最后返回结果
CREATE TEMP TABLE audit_results ON COMMIT DROP AS
EXECUTE (SELECT combined_sql FROM full_query);

SELECT * FROM audit_results;

方法2：生成完整静态查询（替代Excel生成） #

如果Freehand SQL对临时表有限制，可以先运行以下语句生成完整的UNION查询，再复制到数据源中执行：

WITH params AS (
    SELECT
        'DeethJ' AS username,
        '2022-09-01'::date AS from_date,
        '2022-09-30'::date AS to_date
),
target_tables AS (
    SELECT unnest(ARRAY[
        'address',
        'person',
        'person_person_relationship'
    ]) AS table_name
)
SELECT string_agg(
    format(
        $$SELECT '%s' AS "table", id, date_last_edited, last_edited_by FROM %s
          WHERE last_edited_by = '%s'
          AND date_last_edited BETWEEN '%s' AND '%s'$$,
        table_name, table_name, username, from_date, to_date
    ),
    ' UNION '
) AS full_audit_query
FROM target_tables, params;

执行后会直接输出拼接好的完整查询语句，复制后即可在Freehand SQL中使用。

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