I have data that is segmented into:-
My aim is to construct a search function that will look for any text given using the above segmentations in order of precedence. ie. First look for postcode, then town then place
Can this be done efficiently without having to full scan postcode/town before getting on to place? I can identify that the text is a postcode using a REGEX, town and place are more difficult.
I am happy to code this as a PLPGSQL function and made some progress with a strategy along these lines:-
WITH POSTCODES AS (
SELECT postcode FROM postcode WHERE postcode ~* $1
), TOWNS AS (
SELECT town FROM towns WHERE (SELECT * FROM POSTCODES LIMIT 1) IS NULL AND town ~* $1
), PLACES AS (
SELECT place FROM places WHERE (SELECT * FROM TOWNS LIMIT 1) IS NULL AND place ~* $1
)
SELECT postcode as res FROM POSTCODE
UNION ALL
SELECT town as res FROM TOWNS
UNION ALL
SELECT place as res FROM PLACES
I solved this using weightings and combining my data into a single table with columns POSTCODE, TOWN, PLACE which my data allows me to do.
I can then create an additional column tsv as:-
setweight(to_tsvector(COALESCE(postcode,'')), 'A') ||
setweight(to_tsvector(COALESCE(town,'')) , 'B') ||
setweight(to_tsvector(COALESCE(place,'')) , 'C')
And search with:-
WHERE (tsv @@ plainto_tsquery('SN1 3PF'))