postgresql - 查询多个 postgres hstores 结合或

Question

这是我要实现的硬编码示例:

SELECT id FROM places
    WHERE metadata->'route'='Route 23'
    OR  metadata->'route'='Route 22'
    OR  metadata->'region'='Northwest'
    OR  metadata->'territory'='Territory A';

元数据列是一个 hstore 列，我想根据来自不同表的另一个查询动态构建 WHERE 子句。该表可以是:

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

或

    id |    key     |    value    
----+-------------+-------+---
    1  |   route     | Route 23
    2  |   route     | Route 22
    3  |   region    | Northwest
    4  |   territory | Territory A

并不重要，只要能构建 where 子句即可。根据其他查询，它可能有 1 到 n 个 OR。

Answer 1

最终得到了使用第二个表(分布表)的解决方案:

    id   |        metadata           
---------+----------------------------
    1647 | "region"=>"Northwest"
    1648 | "route"=>"Route 23"
    1649 | "route"=>"Route 22"
    1650 | "territory"=>"Territory A"

使用了以下连接，@> 查看 places.metadata 是否包含 distributions.metadata

SELECT places.id, places.metadata
    FROM places INNER JOIN distributions
    ON places.metadata @> distributions.metadata
    WHERE distributions.some_other_column = something;