-- Example table:
create table patient (
id serial primary key,
firstname varchar(100),
lastname varchar(100)
);
insert into patient(firstname, lastname) values ('MARCO ANTONIO', 'PEREZ SANDERS');
-- Create field tsvector field with text columns (preferable using insert and update trigger);
alter table patient add tsv_name tsvector;
-- initially fill field.
update patient set tsv_name = to_tsvector(firstname || ' ' || lastname);
-- create index for fast search:
create index tsv_name_idx on patient using gin(tsv_name);
-- then you can search as:
select * from patient where tsv_name @@ to_tsquery('MARCO & PEREZ');
select * from patient where tsv_name @@ to_tsquery('MARCO & SANDERS');
select * from patient where tsv_name @@ to_tsquery('ANTONIO & PEREZ');
select * from m_paciente
where tsv_nombre @@ to_tsquery(array_to_string(string_to_array('LEIRY SEVERINO',' '),':* & ')||':*');
-- other:
select * from patient where fullname ~ all(array['(?=^LEI.*|\s+LEI\w*)','(?=^ME.*|\s+ME\w*)']);
Fast search using an indexed tsvector field.
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.