PostgreSQL，使用带有 LIKE 和 DISTINCT 的 XML

Question

在开始使用 XML 列作为键/值类型数据的持有者之后，我想知道 PostgreSQL“LIKE”和“DISTINCT”是否可以用于此类数据，我需要什么。

在上一个问题中，我发布了 example table这也涵盖了这些主题的需求。

所以，这样的查询...

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 

通过从存储的 XML 的所有节点中提取所有数据来提供想要的结果。

"{mydatabase,127.0.0.1,john,4424}"
"{herdatabase,127.0.0.1,saly,5432}"

现在我尝试按键值过滤结果:

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/DBUSER/text()', description))::TEXT[] = '{saly}'::TEXT[];

并得到预期的结果:

"{herdatabase,127.0.0.1,saly,5432}"

1) 这是我不知道如何使用 LIKE 而不是 '=' 的问题。

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/DBUSER/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

2) 更好的说法是我希望在所有可用数据下进行“喜欢”搜索，如下所示:

SELECT myindex, xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/node()/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

但这也行不通。

3) 为了在现实世界中更精确，这里需要使用 DISTINCT 标准从 XML 中过滤可用数据，因为相同的数据会出现更多次。像这样:

SELECT DISTINCT xpath('/setup/node()/text()', description) FROM temp1 
   WHERE (xpath('/setup/node()/text()', description))::TEXT[] LIKE 'aly'::TEXT[];

我现在已经描述了功能，但是数据和文本列类型困惑，为了获得更有条理的数据，我想切换到 XML。

请根据需要帮助获得答案。

编辑Postgres 9.3， window 7。这方面的数据在这里:PostgreSQL, using xml

这是我的查询:

SELECT LAST(myindex), LAST(description), content::text, LAST(content)
  FROM (SELECT myindex, description, 
       (xpath('/setup/node()/text()', description)) AS content FROM temp1) AS alias
 WHERE content::text ILIKE '%127%'
 GROUP BY content::text;

通过尝试自己进行查询，我发现使用 group by 比使用 distinct 更好。
它有点慢但可行。
一些经验认为，那些查询可以吗？

Answer 1

您可以使用 UNNEST() 将子查询中的 XML 数组拆分为行，然后使用 ILIKE 检查这些值。

SELECT myindex, description, content
FROM (
    SELECT myindex, description, UNNEST(xpath('/setup/node()/text()', description))::TEXT AS content
    FROM temp1
    ) AS alias
WHERE content ILIKE '%127%'

我没有对它进行基准测试，但性能应该更好。