hstore

Этот модуль реализует тип данных hstore для хранения наборов пар ключ/значение внутри одного значения QHB. Это может быть полезно в различных сценариях, например для строк со множеством редко проверяемых атрибутов или частично структурированных данных. Ключи и значения представляют собой простые текстовые строки.

Этот модуль считается «доверенным», то есть его могут устанавливать обычные пользователи с правом CREATE в текущей базе данных.

Внешнее представление hstore

Текстовое представление типа hstore, применяемое для ввода и вывода, включает ноль или более пар ключ => значение, разделенных запятыми. Несколько примеров:

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

Порядок пар не имеет значения (и может не воспроизводиться в выводе). Пробелы между парами и вокруг знака => игнорируются. Ключи и значения, содержащие пробелы, запятые и знаки = или >, нужно заключать в кавычки. Чтобы включить в ключ или значение обратный слэш или кавычки, следует экранировать его обратным слэшем.

Все ключи в hstore уникальны. Если объявить hstore с дублирующимися ключами, в нем будет сохранен только один ключ без гарантии выбора, какой именно:

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>"1"

Значением (но не ключом) может быть SQL NULL. Например:

key => NULL

Ключевое слово NULL нечувствительно к регистру. Чтобы NULL воспринимался как обычная строка «NULL», следует заключить его в кавычки.

Примечание
Учтите, что когда текстовый формат hstore используется для ввода данных, он применяется до всех требуемых кавычек или управляющих символов. Если вы передаете литерал hstore в параметре, в дополнительной обработке нет необходимости. Но если вы передаете его в виде строковой константы в кавычках, то все символы апострофов и (в зависимости от параметра конфигурации standard_conforming_strings) обратного слэша нужно корректно экранировать. Подробнее о работе со строковыми константами можно узнать в подразделе Строковые константы.

При выводе ключи и значения всегда заключаются в кавычки, даже если в этом нет особой необходимости.

Операторы и функции hstore

Операторы, предоставляемые модулем hstore, перечислены в Таблице 7, функции — в Таблице 8.

Таблица 7. Операторы hstore

ОператорОписаниеПример(ы)
hstore -> text → textВозвращает значение, связанное с заданным ключом, или NULL, если ключ отсутствует.'a=>x, b=>y'::hstore -> 'a' → x
hstore -> text[] → text[]Возвращает значения, связанные с заданными ключами, или NULL, если ключи отсутствуют.'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] → {"z","x"}
hstore || hstore → hstoreКонкатенирует два набора hstores.'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore → "a"=>"b", "c"=>"x", "d"=>"q"
hstore ? text → booleanНабор hstore содержит ключ?'a=>1'::hstore ? 'a' → t
hstore ?& text[] → booleanНабор hstore содержит все указанные ключи?'a=>1,b=>2'::hstore ?& ARRAY['a','b'] → t
hstore ?| text[] → booleanНабор hstore содержит какой-либо из указанных ключей?'a=>1,b=>2'::hstore ?| ARRAY['b','c'] → t
hstore @> hstore → booleanЛевый операнд содержит правый?'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' → t
hstore <@ hstore → booleanЛевый операнд содержится в правом?'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' → f
hstore - text → hstoreУдаляет ключ из левого операнда.'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3"
hstore - text[] → hstoreУдаляет ключи из левого операнда.'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] → "c"=>"3"
hstore - hstore → hstoreУдаляет из левого операнда пары ключ/значение, совпадающие с парами в правом.'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3"
anyelement #= hstore → anyelementЗаменяет поля в левом операнде (который должен иметь составной тип) соответствующими значениями из hstore.ROW(1,3) #= 'f1=>11'::hstore → (11,3)
%% hstore → text[]Преобразует hstore в массив перемежающихся ключей и значений.%% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}
%# hstore → text[]Преобразует hstore в двумерный массив ключей/значений.%# 'a=>foo, b=>bar'::hstore → {{a,foo},{b,bar}}

Таблица 8. Функции hstore

ФункцияОписаниеПример(ы)
hstore ( record ) → hstoreФормирует hstore из записи или кортежа.hstore(ROW(1,2)) → "f1"=>"1", "f2"=>"2"
hstore ( text[] ) → hstoreФормирует hstore из массива, который может быть двумерным или содержать перемежающиеся ключи/значения.hstore(ARRAY['a','1','b','2']) → "a"=>"1", "b"=>"2"
hstore(ARRAY[['c','3'],['d','4']]) → "c"=>"3", "d"=>"4"
hstore ( text[], text[] ) → hstoreФормирует hstore из отдельных массивов ключей и значений.hstore(ARRAY['a','b'], ARRAY['1','2']) → "a"=>"1", "b"=>"2"
hstore ( text, text ) → hstoreФормирует hstore с одним элементом.hstore('a', 'b') → "a"=>"b"
akeys ( hstore ) → text[]Извлекает ключи из hstore в виде массива.akeys('a=>1,b=>2') → {a,b}
skeys ( hstore ) → setof textИзвлекает ключи из hstore в виде множества.skeys('a=>1,b=>2') →
a
b
avals ( hstore ) → text[]Извлекает значения из hstore в виде массива.avals('a=>1,b=>2') → {1,2}
svals ( hstore ) → setof textИзвлекает значения из hstore в виде множества.svals('a=>1,b=>2') →
1
2
hstore_to_array ( hstore ) → text[]Извлекает ключи и значения из hstore в виде массива перемежающихся ключей и значений.hstore_to_array('a=>1,b=>2') → {a,1,b,2}
hstore_to_matrix ( hstore ) → text[]Извлекает ключи и значения из hstore в виде двумерного массива.hstore_to_matrix('a=>1,b=>2') → {{a,1},{b,2}}
hstore_to_json ( hstore ) → jsonПреобразует hstore в json, конвертируя все отличные от NULL в строки JSON.
Эта функция используется неявно, когда значение hstore приводится к типу json.
hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_jsonb ( hstore ) → jsonbПреобразует hstore в jsonb, конвертируя все отличные от NULL в строки JSON.
Эта функция используется неявно, когда значение hstore приводится к типу jsonb.
hstore_to_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
hstore_to_json_loose ( hstore ) → jsonПреобразует hstore в json, но при этом пытается распознать числовые и логические и передать их в JSON без кавычек.hstore_to_json_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
hstore_to_jsonb_loose ( hstore ) → jsonbПреобразует hstore в jsonb, но при этом пытается распознать числовые и логические и передать их в JSON без кавычек.hstore_to_jsonb_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
slice ( hstore, text[] ) → hstoreИзвлекает из hstore подмножество, содержащее только указанные ключи.slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x']) → "b"=>"2", "c"=>"3"
each ( hstore ) → setof record ( key text, value text )Извлекает из hstore ключи и значения в виде набора записей.select * from each('a=>1,b=>2') →
key | value
-----+-------
a | 1
b | 2
exist ( hstore, text ) → booleanhstore содержит ключ?exist('a=>1', 'a') → t
defined ( hstore, text ) → booleanhstore содержит отличное от NULL значение для ключа?defined('a=>NULL', 'a') → f
delete ( hstore, text ) → hstoreУдаляет пару с соответствующим ключом.delete('a=>1,b=>2', 'b') → "a"=>"1"
delete ( hstore, text[] ) → hstoreУдаляет пары с соответствующими ключами.delete('a=>1,b=>2,c=>3', ARRAY['a','b']) → "c"=>"3"
delete ( hstore, hstore ) → hstoreУдаляет пары, соответствующие парам во втором аргументе.delete('a=>1,b=>2', 'a=>4,b=>2'::hstore) → "a"=>"1"
populate_record ( anyelement, hstore ) → anyelementЗаменяет поля в левом операнде (который должен иметь составной тип) соответствующими значениями из hstore.populate_record(ROW(1,2), 'f1=>42'::hstore) → (42,2)

Помимо этих операторов и функций к значениям типа hstore можно обратиться по индексу, позволяя им действовать подобно ассоциативным массивам. Можно указать только один индекс типа text; он воспринимается как ключ, для которого извлекается или сохраняется соответствующее значение. Например:

CREATE TABLE mytable (h hstore);
INSERT INTO mytable VALUES ('a=>b, c=>d');
SELECT h['a'] FROM mytable;
 h
---
 b
(1 row)

UPDATE mytable SET h['c'] = 'new';
SELECT h FROM mytable;
          h
----------------------
 "a"=>"b", "c"=>"new"
(1 row)

При обращении по индексу возвращается NULL, если индекс — NULL или указанный ключ не существует в hstore. (Таким образом, обращение по индексу не сильно отличается от оператора ->.) Изменение по индексу не выполняется, если индекс — NULL; в остальных случаях заменяется значение указанного ключа (если такой ключ еще не существует, он добавляется в hstore).

Индексы

Тип hstore поддерживает индексы GiST и GIN для операторов @>, ?, ?& и ?|. Например:

CREATE INDEX hidx ON testhstore USING GIST (h);

CREATE INDEX hidx ON testhstore USING GIN (h);

Класс операторов GiST gist_hstore_ops аппроксимирует набор пар ключ/значение как сигнатуру битовой карты. Его необязательный целочисленный параметр siglen определяет длину сигнатуры в байтах. Значение по умолчанию — 16 байтов. Допустима длина сигнатуры в пределах от 1 до 2024 байтов. При увеличении размера сигнатур поиск работает точнее (сканируется меньшая область индекса и меньше страниц кучи), но сам индекс становится больше.

Пример создания такого индекса с длиной сигнатуры 32 байта:

CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));

Тип hstore также поддерживает индексы btree и hash для оператора =. Это позволяет объявлять столбцы hstore как уникальные (UNIQUE) или использовать их в выражениях GROUP BY, ORDER BY или DISTINCT. Порядок сортировки значений hstore не имеет особого смысла, но эти индексы могут быть полезны для поиска по равенству. Создать индексы для сравнений с помощью = можно так:

CREATE INDEX hidx ON testhstore USING BTREE (h);

CREATE INDEX hidx ON testhstore USING HASH (h);

Примеры

Присвоение значения новому или уже существующему ключу:

UPDATE tab SET h['c'] = '3';

Другой способ сделать то же самое:

UPDATE tab SET h = h || hstore('c', '3');

Если в одной операции нужно добавить или изменить несколько ключей, эффективнее использовать конкатенацию, а не обращение по индексу:

UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);

Удаление ключа:

UPDATE tab SET h = delete(h, 'k1');

Преобразование типа record в тип hstore:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore
---------------------------------------------
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

Преобразование типа hstore в предопределенный тип record:

CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
                              '"col1"=>"456", "col2"=>"zzz"');
 col1 | col2 | col3
------+------+------
  456 | zzz  |
(1 row)

Изменение существующей записи с использованием значений из hstore:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 | col2 | col3
------+------+------
  123 | foo  | baz
(1 row)

Статистика

Из-за свойственной ему либеральности тип hstore может содержать множество различных ключей. Проверка ключей на допустимость является задачей этого приложения. Следующие примеры демонстрируют несколько приемов проверки ключей и получения статистики.

Простой пример:

SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');

С использованием таблицы:

CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;

Актуальная статистика:

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM testhstore) AS stat
  GROUP BY key
  ORDER BY count DESC, key;
    key    | count
-----------+-------
 line      |   883
 query     |   207
 pos       |   203
 node      |   202
 space     |   197
 status    |   195
 public    |   194
 title     |   190
 org       |   189
...................

Трансформации

Также имеются дополнительные расширения, которые реализуют трансформацию типа hstore для языков PL/Perl и PL/Python. Расширения для PL/Perl называются hstore_plperl и hstore_plperlu, для доверенного и недоверенного PL/Perl соответственно. Если вы установите эти трансформации и укажите их при создании функции, значения hstore будут отображаться в хэши Perl. Расширения для PL/Python называются hstore_plpythonu, hstore_plpython2u и hstore_plpython3u. Если вы воспользуетесь ими, значения hstore будут отображаться в словари Python.

Внимание
Настоятельно рекомендуется устанавливать расширения трансформации в одну схему с hstore. Если выбрать какую-либо другую схему, которая может содержать объекты, определенные злонамеренным пользователем, то при установке расширения возникнут угрозы безопасности.

Авторы

Олег Бартунов (oleg@sai.msu.su), Москва, Московский государственный университет имени М. В. Ломоносова, Россия

Федор Сигаев (teodor@sigaev.ru), Москва, ООО «Дельта-Софт», Россия

Дополнительные улучшения внес Эндрю Гирт (andrew@tao11.riddles.org.uk), Великобритания