在PostgreSQL中搜索多个列时，如何获得匹配的列名

Question

在PostgreSQL中搜索多个列时，有没有办法获得匹配的列名？

假设我有以下表格结构和查询：

CREATE TABLE document (
id serial PRIMARY KEY,
document_content VARCHAR
);
CREATE TABLE story (
id serial PRIMARY KEY,
headline VARCHAR
);
-----
SELECT
"document".*,
story.id,
story.headline
FROM
"document"
INNER JOIN story_document AS Documents_join ON "document".id = Documents_join.document_id
INNER JOIN story ON  "story".id = Documents_join.story_id
WHERE to_tsvector(document_content) @@ to_tsquery('foo')
OR to_tsvector(headline) @@ to_tsquery('foo');

我想把这两列的值连接起来，运行全文搜索，然后为这两列创建一个子查询，然后单独重新运行搜索并记录结果作为参考，但这意味着执行搜索3x:

SELECT
"document".*,
story.id AS story_id,
story.headline
(SELECT "document".id WHERE to_tsvector(document_content) @@ to_tsquery('foo')) AS "matching_document_id",
(SELECT story_id WHERE to_tsvector(headline) @@ to_tsquery('foo')) AS "matching_story_id"
FROM
"document"
INNER JOIN story_document AS Documents_join ON "document".id = Documents_join.document_id
RIGHT JOIN story ON "story".id = Documents_join.story_id
WHERE to_tsvector(document_content || ' ' || headline) @@ to_tsquery('foo');

我如何获得对该列的引用：document_content或headline，其中关键字"；foo"；在一个查询中找到？

谢谢！

Answer 1

由于列在不同的表中，所以您能做的最好的事情就是将OR转换为UNION:

SELECT
"document".*,
story.id,
story.headline
FROM
"document"
INNER JOIN story_document AS Documents_join ON "document".id = Documents_join.document_id
INNER JOIN story ON  "story".id = Documents_join.story_id
WHERE to_tsvector(document_content) @@ to_tsquery('foo')
UNION
SELECT
"document".*,
story.id,
story.headline
FROM
"document"
INNER JOIN story_document AS Documents_join ON "document".id = Documents_join.document_id
INNER JOIN story ON  "story".id = Documents_join.story_id
WHERE to_tsvector(headline) @@ to_tsquery('foo');

然后PostgreSQL不必仅仅为了过滤掉大部分行而构建完整的联接。如果条件是选择性的和索引的，并且您对联接条件也有索引，那么我的变体将是快速的，这样您就可以获得快速的嵌套循环联接。

以下是有关处理OR的更多信息。