请求协助创建包含物化视图的API端点信息物化视图

以下是整合了表、视图、物化视图、函数，同时包含权限信息与API端点的完整查询代码：

SELECT DISTINCT
    COALESCE(t.table_catalog, r.routine_catalog) AS catalog,
    COALESCE(t.table_schema, r.routine_schema) AS schema,
    COALESCE(t.table_name, r.routine_name) AS name,
    -- 统一对象类型名称
    CASE
        WHEN t.table_type IS NOT NULL THEN t.table_type
        WHEN r.routine_type IS NOT NULL THEN r.routine_type
    END AS type,
    COALESCE(tp.grantee, rp.grantee) AS user_name,
    -- 生成API端点：表/物化视图/视图用 schema/name，函数用 rpc/name
    CASE
        WHEN t.table_type IS NOT NULL THEN format('/%s/%s', t.table_schema, t.table_name)
        WHEN r.routine_type IS NOT NULL THEN format('/rpc/%s', r.routine_name)
    END AS endpoint
FROM 
    -- 从information_schema.tables获取表、视图、物化视图
    (SELECT * FROM information_schema.tables WHERE table_type IN ('BASE TABLE', 'VIEW', 'MATERIALIZED VIEW')) AS t
    FULL OUTER JOIN 
    -- 从information_schema.routines获取函数
    (SELECT * FROM information_schema.routines) AS r
    ON t.table_catalog = r.routine_catalog
        AND t.table_schema = r.routine_schema
        AND t.table_name = r.routine_name
    -- 关联表/视图/物化视图的权限
    LEFT OUTER JOIN information_schema.table_privileges AS tp 
    ON t.table_catalog = tp.table_catalog 
        AND t.table_schema = tp.table_schema 
        AND t.table_name = tp.table_name 
    -- 关联函数的权限
    LEFT OUTER JOIN information_schema.routine_privileges AS rp 
    ON r.routine_catalog = rp.routine_catalog 
        AND r.routine_schema = rp.routine_schema 
        AND r.routine_name = rp.routine_name
WHERE 
    -- 过滤表/视图/物化视图的条件
    (
        t.table_schema IS NOT NULL
        AND t.table_schema NOT IN ('pg_catalog', 'information_schema', 'api_informations', 'public', 'cron')
        AND tp.grantee NOT IN ('usersi_adm', 'organisationnel_adm')
    )
    OR
    -- 过滤函数的条件
    (
        r.routine_schema IS NOT NULL
        AND r.routine_schema NOT IN ('pg_catalog', 'presentation', 'information_schema', 'api_informations', 'public', 'cron')
        AND rp.grantee NOT IN ('PUBLIC', 'usersi_adm')
    );

关键说明 #

1. 对象覆盖：通过information_schema.tables的table_type筛选，直接包含了普通表、视图、物化视图，无需额外关联系统底层表
2. 权限关联：用table_privileges关联表/视图/物化视图的权限，routine_privileges关联函数权限，通过COALESCE统一返回权限用户信息
3. 端点生成：根据对象类型生成对应格式的API端点，可根据业务需求调整格式规则
4. 过滤逻辑：保留了你原有的排除系统Schema和特定用户的条件，确保结果仅包含业务相关对象

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