基于其他预处理列在postgres中取数

Question

在我的数据库表（whitelist_domain_data）中，我有字段id、url、data

Url列有多个Url，如

http://www.dailystrength.org/c/Hidradenitis_Suppurativa/forum/8870995-solodyn-135-mg-works
http://au.answers.yahoo.com/question/index?qid=20090325215905AA6UVOa
http://navaspot.wordpress.com                                                              

我要获取具有相同域的行。在

表：白名单

模式：id、url、数据

^{pr2}$

应返回：

dailystrength.org   200

问题： 如果url有域，如何设计查询以获取所有url数据“dailystrength.org网站"? 在

Answer 1

可以使用substring（）执行此操作，而且可能还需要使用表达式索引。下面是一个示例（我调整了正则表达式以匹配我认为您想要的）：

CREATE TABLE whitelist_domain_data(
  id SERIAL PRIMARY KEY,
  url TEXT NOT NULL
);

CREATE INDEX domain_index
  ON whitelist_domain_data (substring(url from 'https?\:\/\/([a-z0-9\.]+)'));

INSERT into whitelist_domain_data (url) VALUES
  ('http://www.dailystrength.org/c/Hidradenitis_Suppurativa/forum/8870995-solodyn-135-mg-works'),
  ('http://au.answers.yahoo.com/question/index?qid=20090325215905AA6UVOa'),
  ('http://navaspot.wordpress.com');

SELECT * FROM whitelist_domain_data WHERE substring(url from 'https?\:\/\/([a-z0-9\.]+)') = 'www.dailystrength.org';

现在这个查询可以使用索引。如果您计划大量使用此功能，则可以考虑为其创建一个特定的函数：

^{pr2}$

则上述内容变为：

CREATE INDEX domain_index
  ON whitelist_domain_data (get_domain(url));

SELECT * FROM whitelist_domain_data WHERE get_domain(url) = 'www.dailystrength.org';

因此，如果你想改变什么是一个域（忽略子域，或其他什么），你只需改变函数，你的查询仍然有效。不过，我想你得重新编制索引。在

我检查了这一切在postgres9.1上都可以工作，但它应该与任何最新版本兼容。表达式索引和substring（）都可以追溯到7.x天。在