Let's say I have a table representing principal country divisions (ex States):
create table principal_country_divisions (
id int primary key,
name text not null,
country_code char(2)
);
insert into principal_country_divisions values (1, 'New York', 'US');
I want users to be easily able to find New York via synonyms such as 'New York', 'NY', or 'New York State'.
So I have a synonyms table:
create table synonyms (
syn text,
name text,
primary key (syn, name)
);
insert into synonyms values
('NY', 'New York'),
('New York State', 'New York');
What is an efficient and easy way to query this and return ONE record for New York?
In particular, they should be able to find the result for the default name 'New York' OR any synonym:
select * from principal_country_divisions where name = 'NY';
result: {1, 'New York', 'US'}
I guess I would start with something like this:
select
id,
name,
country_code
from principal_country_divisions a
where name = 'NY'
or exists (select 1 from synonyms where name = a.name and syn = 'NY')
Can I do this only using a view, or should I use a function?