28 lines
1.3 KiB
MySQL
28 lines
1.3 KiB
MySQL
|
ALTER TABLE jam_tracks ADD COLUMN search_tsv tsvector;
|
||
|
|
ALTER TABLE jam_tracks ADD COLUMN artist_tsv tsvector;
|
||
|
|
ALTER TABLE jam_tracks ADD COLUMN name_tsv tsvector;
|
||
|
|
|
||
|
|
CREATE FUNCTION jam_tracks_update_tsv() RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
new.search_tsv = to_tsvector('public.jamenglish', COALESCE(NEW.original_artist, '') || ' ' || COALESCE(NEW.name, '') || ' ' || COALESCE(NEW.additional_info, ''));
|
||
|
|
new.artist_tsv = to_tsvector('public.jamenglish', COALESCE(NEW.original_artist, ''));
|
||
|
|
new.name_tsv = to_tsvector('public.jamenglish', COALESCE(NEW.name, ''));
|
||
|
|
|
||
|
|
RETURN NEW;
|
||
|
|
END
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
|
||
|
|
ON jam_tracks FOR EACH ROW EXECUTE PROCEDURE
|
||
|
|
jam_tracks_update_tsv();
|
||
|
|
|
||
|
|
CREATE INDEX jam_tracks_search_tsv_index ON jam_tracks USING gin(search_tsv);
|
||
|
|
CREATE INDEX jam_tracks_artist_tsv_index ON jam_tracks USING gin(artist_tsv);
|
||
|
|
CREATE INDEX jam_tracks_name_tsv_index ON jam_tracks USING gin(name_tsv);
|
||
|
|
|
||
|
|
CREATE INDEX jam_tracks_name_key ON jam_tracks USING btree (name);
|
||
|
|
CREATE INDEX jam_tracks_original_artist_key ON jam_tracks USING btree (original_artist);
|
||
|
|
CREATE INDEX jam_tracks_status_key ON jam_tracks USING btree (status);
|
||
|
|
|
||
|
|
|
||
|
|
UPDATE jam_tracks SET original_artist=original_artist, name=name, additional_info=additional_info;
|