PostgreSQL如何关联通配符路径表与文件表生成带类型的视图

最简实现方案（JOIN方式，适合创建视图） #

直接通过关联条件做模糊匹配即可，不需要拆分两步查询，视图创建语句如下：

CREATE OR REPLACE VIEW file_with_type AS
SELECT
  t2.file,
  t2.path,
  t1.type
FROM table2 t2
LEFT JOIN table1 t1
  ON t2.path LIKE REPLACE(t1.path, '*', '%');

该视图会完整保留table2的所有数据：

• 若单个文件路径匹配多条table1的规则，会生成对应多条不同type的记录，符合你后续按type筛选的需求，例如查询所有sys类型的文件只需执行：

  SELECT * FROM file_with_type WHERE type = 'sys'
  

• 若需要单个文件仅返回一条记录，多个匹配的type合并为数组存储，可以调整为聚合写法：

  CREATE OR REPLACE VIEW file_with_type AS
  SELECT
    t2.file,
    t2.path,
    ARRAY_AGG(DISTINCT t1.type) AS types
  FROM table2 t2
  LEFT JOIN table1 t1
    ON t2.path LIKE REPLACE(t1.path, '*', '%')
  GROUP BY t2.file, t2.path;
  

合并原有两步查询的写法 #

如果你希望沿用数组匹配的逻辑，可直接将第一步查询作为子查询传入ANY参数：

SELECT * FROM table2
WHERE path LIKE ANY (
  SELECT array_agg(REPLACE(path,'*','%'))
  FROM table1
  WHERE type = 'sys'
  GROUP BY type
);

该写法适合单次查询指定type的场景，通用性不如JOIN创建的全量视图。

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