Full text search
CREATE TABLE document (
doc_id varchar PRIMARY KEY,
title varchar,
content varchar
);
CREATE TABLE doc_spec_sets (
tc_set_id varchar PRIMARY KEY,
doc_id varchar,
spec varchar
);
insert into document values('doc1', 'quickly for the visualiser that', 'with the structure I mentioned');
insert into document values('doc2', 'The main arguments', 'if your data was static');
insert into document values('doc3', 'We’ve had issues', 'columns so we need');
insert into document values('doc4', 'assessed our options', 'Replace both occurences');
insert into document values('doc5', 'So even though', 'full text of documents');
insert into document values('doc6', 'records and we found', 'documents, it added');
insert into document values('doc7', 'the speed deteriorates', 'rather than a slower query');
insert into document values('doc8', 'but we found', 'network overhead rather');
insert into document values('doc9', 'Then you need to fill options', 'then it only added');
insert into doc_spec_sets values('tc1', 'doc1', 'documents');
insert into doc_spec_sets values('tc2', 'doc2', 'occurences');
insert into doc_spec_sets values('tc3', 'doc3', 'rather');
insert into doc_spec_sets values('tc4', 'doc4', 'options');
insert into doc_spec_sets values('tc5', 'doc5', 'documents');
insert into doc_spec_sets values('tc6', 'doc6', 'mentioned');
insert into doc_spec_sets values('tc7', 'doc3', 'options');
insert into doc_spec_sets values('tc8', 'doc4', 'structure');
insert into doc_spec_sets values('tc9', 'doc1', 'network');
CREATE OR REPLACE FUNCTION make_tsvector(title varchar, content varchar)
RETURNS tsvector AS $$
BEGIN
RETURN (setweight(to_tsvector('english', title),'A') ||
setweight(to_tsvector('english', content), 'B'));
END
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE INDEX IF NOT EXISTS idx_fts_articles ON document
USING gin(make_tsvector(title, content));
SELECT
doc_id,
title,
content
FROM document
WHERE
make_tsvector(title, content) @@ to_tsquery('documents & added');
select
document.doc_id,
document.title,
document.content,
doc_spec_sets.spec
from doc_spec_sets
LEFT OUTER join document on doc_spec_sets.doc_id = document.doc_id
where
doc_spec_sets.spec = 'documents'
or
doc_spec_sets.spec = 'added'
group by document.doc_id, doc_spec_sets.spec;
|
run
| edit
| history
| help
|
0
|
|
|