How to get the matching column name when searching across multiple columns in PostgreSQL?

Question

Is there a way to get the matching column name when searching across multiple columns in PostgreSQL?

Say I have the following table structure and query:

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');

I was thinking of concatenating the value of the two columns, run the full text search, then create a sub query for both columns and re-run the search individually and record the result as a reference, but this would mean executing the search 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');

How could I get a reference to the column: document_content or headline, where the keyword "foo" was found in one query?

Thanks!

Answer 1

Since the columns are in different tables the best you can do is translate the OR into a 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');

Then PostgreSQL doesn't have to build the complete join just to filter out most of the rows. My variant will be fast if the conditions are selective and indexed and you have indexes on the join conditions as well, so that you can get fast nested loop joins.

Here is some more about dealing with OR.