Ответ 1
Праведный путь
Возможно, вы захотите пересмотреть нормализовать вашу схему. Каждому не нужно "присоединяться даже к самому простому запросу". Создайте VIEW.
Таблица может выглядеть так:
CREATE TABLE hostname (
hostname_id serial PRIMARY KEY
,host_id int REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE
,hostname text UNIQUE
);
Первичный первичный ключ hostname_id не является обязательным. Я предпочитаю иметь его. В вашем случае hostname может быть основным ключом. Но многие операции выполняются быстрее с помощью простого маленького ключа integer. Создайте ограничение внешнего ключа для ссылки на таблицу host.
Создайте такой вид:
CREATE VIEW v_host AS
SELECT h.*
,array_agg(hn.hostname) AS hostnames
-- ,string_agg(hn.hostname, ', ') AS hostnames -- text instead of array
FROM host h
JOIN hostname hn USING (host_id)
GROUP BY h.host_id; -- works in v9.1+
Начиная с pg 9.1, первичный ключ в GROUP BY охватывает все столбцы этой таблицы в списке SELECT. примечания к выпуску для версии 9.1:
Разрешить столбцы не
GROUP BYв списке целей запроса, когда первичный ключ указан в предложенииGROUP BY
Запросы могут использовать представление, подобное таблице. Поиск имени хоста будет намного быстрее:
SELECT *
FROM host h
JOIN hostname hn USING (host_id)
WHERE hn.hostname = 'foobar';
Если у вас есть индекс на host(host_id), это должно быть так, как должно быть основным ключом. Кроме того, ограничение UNIQUE на hostname(hostname) автоматически реализует другой необходимый индекс.
В Postgres 9.2 + многоколоночный индекс будет еще лучше, если вы сможете получить просмотр только для индексирования:
CREATE INDEX hn_multi_idx ON hostname (hostname, host_id)
Начиная с Postgres 9.3, вы можете использовать MATERIALIZED VIEW, что позволяет обстоятельства. Особенно, если вы читаете гораздо чаще, чем вы пишете в таблицу.
Темная сторона (что вы на самом деле просили)
Если я не могу убедить вас в праведном пути, я тоже помогу на темной стороне. Я гибкий.:)
Вот демонстрация того, как применять уникальность имен хостов. Я использую таблицу hostname для сбора имен хостов и триггера в таблице host, чтобы обновлять его. Уникальные нарушения вызывают ошибку и прерывают операцию.
CREATE TABLE host(hostnames text[]);
CREATE TABLE hostname(hostname text PRIMARY KEY); -- pk enforces uniqueness
Функция триггера
CREATE OR REPLACE FUNCTION trg_host_insupdelbef()
RETURNS trigger AS
$func$
BEGIN
-- split UPDATE into DELETE & INSERT
IF TG_OP = 'UPDATE' THEN
IF OLD.hostnames IS DISTINCT FROM NEW.hostnames THEN -- keep going
ELSE RETURN NEW; -- exit, nothing to do
END IF;
END IF;
IF TG_OP IN ('DELETE', 'UPDATE') THEN
DELETE FROM hostname h
USING unnest(OLD.hostnames) d(x)
WHERE h.hostname = d.x;
IF TG_OP = 'DELETE' THEN RETURN OLD; -- exit, we are done
END IF;
END IF;
-- control only reaches here for INSERT or UPDATE (with actual changes)
INSERT INTO hostname(hostname)
SELECT h
FROM unnest(NEW.hostnames) h;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER host_insupdelbef
BEFORE INSERT OR DELETE OR UPDATE OF hostnames ON host
FOR EACH ROW EXECUTE PROCEDURE trg_host_insupdelbef();
SQL Fiddle с тестовым прогоном.
Используйте индекс GIN в столбце массива host.hostnames и операторы массива для работы с ним: