Untitled

-- FTS prepare
ALTER TABLE address ADD COLUMN source_tsvector tsvector NOT NULL DEFAULT to_tsvector(source);

DROP INDEX address.address_value_gin_idx;

DROP INDEX address.address_source_gin_idx;

CREATE INDEX address_source_gin_idx ON address USING gin (source_tsvector);

CREATE OR REPLACE FUNCTION before_insert_address_function()
RETURNS TRIGGER AS $$
    BEGIN
        NEW.source_tsvector := to_tsvector('russian', NEW.source);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_address
BEFORE INSERT ON address
FOR EACH ROW
EXECUTE FUNCTION before_insert_address_function();

CREATE OR REPLACE FUNCTION before_update_address_function()
RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.source IS DISTINCT FROM NEW.source THEN
            NEW.source_tsvector := to_tsvector('russian', NEW.source);
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

-- FTS Поиск
WITH source_query AS (
    SELECT
        garbage_source.geo_object_id,
        garbage_source.name,
        geo_object.type_id,
        garbage_source.latitude AS lat,
        garbage_source.longitude AS lon,
        ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(garbage_source.name), plainto_tsquery('Борисовское')) AS rank
    FROM garbage_source
        LEFT JOIN geo_object ON garbage_source.geo_object_id = geo_object.id
        LEFT JOIN address ON garbage_source.address_id = address.id
    WHERE garbage_source.latitude IS NOT NULL
        AND garbage_source.longitude IS NOT NULL
        AND (
            to_tsvector(garbage_source.name) @@ plainto_tsquery('Борисовское')
            OR address.source_tsvector @@ plainto_tsquery('Борисовское')
        )
), msn_query AS (
    SELECT
        msn.geo_object_id,
        msn.name,
        geo_object.type_id,
        msn.latitude AS lat,
        msn.longitude AS lon,
        ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(msn.name), plainto_tsquery('Борисовское')) AS rank
    FROM msn
        LEFT JOIN geo_object ON msn.geo_object_id = geo_object.id
        LEFT JOIN address ON msn.address_id = address.id
    WHERE msn.latitude IS NOT NULL
        AND msn.longitude IS NOT NULL
        AND (
            to_tsvector(msn.name) @@ plainto_tsquery('Борисовское')
            OR address.source_tsvector @@ plainto_tsquery('Борисовское')
        )
), tko_object_query AS (
    SELECT
        tko_object.geo_object_id,
        tko_object.name,
        geo_object.type_id,
        tko_object.latitude AS lat,
        tko_object.longitude AS lon,
        ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(tko_object.name), plainto_tsquery('Борисовское')) AS rank
    FROM tko_object
        LEFT JOIN geo_object ON tko_object.geo_object_id = geo_object.id
        LEFT JOIN address ON tko_object.address_id = address.id
    WHERE tko_object.latitude IS NOT NULL
        AND tko_object.longitude IS NOT NULL
        AND (
            to_tsvector(tko_object.name) @@ plainto_tsquery('Борисовское')
            OR address.source_tsvector @@ plainto_tsquery('Борисовское')
        )
), threads_query AS (
    SELECT
        geo_object.id AS geo_object_id,
        geo_object.name,
        geo_object.type_id,
        geo_object.lat,
        geo_object.lon,
        ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) AS rank
    FROM threads
        INNER JOIN geo_object ON threads.geo_object_id = geo_object.id
        LEFT JOIN address ON geo_object.id = address.geo_object_id
    WHERE geo_object.lat IS NOT NULL
        AND geo_object.lon IS NOT NULL
        AND (
            to_tsvector(geo_object.name) @@ plainto_tsquery('Борисовское')
            OR address.source_tsvector @@ plainto_tsquery('Борисовское')
        )
)
SELECT geo_object_id, name, lat, lon, type_id, rank FROM source_query
UNION
SELECT geo_object_id, name, lat, lon, type_id, rank FROM msn_query
UNION
SELECT geo_object_id, name, lat, lon, type_id, rank FROM tko_object_query
UNION
SELECT geo_object_id, name, lat, lon, type_id, rank FROM threads_query
ORDER BY rank DESC;