F.44. pg_variables

Модуль pg_variables содержит функции для работы с переменными различных типов. Созданные переменные существуют в течение текущего пользовательского сеанса.

F.44.1. Установка

Расширение pg_variables включено в состав Postgres Pro. Установив Postgres Pro, вы должны выполнить команду CREATE EXTENSION, чтобы подготовить pg_variables к работе, следующим образом:

CREATE EXTENSION pg_variables;

F.44.2. Использование

Модуль pg_variables содержит функции, позволяющие создавать переменные скалярных типов, переменные-записи и переменные-массивы, читать их и управлять ими. Синтаксис и описания функций представлены в следующих разделах:

Разобранные примеры использования вы можете найти в Подразделе F.44.5.

F.44.2.1. Использование транзакционных переменных

По умолчанию переменные создаются как нетранзакционные. Успешно созданная переменная продолжает существовать на протяжении всего сеанса, вне зависимости от возможных откатов транзакций. Например:

SELECT pgv_set('vars', 'int1', 101);
BEGIN;
SELECT pgv_set('vars', 'int2', 102);
ROLLBACK;

SELECT * FROM pgv_list() order by package, name;
 package | name | is_transactional
---------+------+------------------
 vars    | int1 | f
 vars    | int2 | f

Если вы хотите использовать переменную с поддержкой транзакций и точек сохранения, передайте дополнительный флаг is_transactional в последнем параметре функции, создающей переменную:

BEGIN;
SELECT pgv_set('vars', 'trans_int', 101, true);
SAVEPOINT sp1;
SELECT pgv_set('vars', 'trans_int', 102, true);
ROLLBACK TO sp1;
COMMIT;
SELECT pgv_get('vars', 'trans_int', NULL::int);
 pgv_get
---------
     101

Вы должны указывать флаг is_transactional при каждом изменении значения транзакционной переменной с помощью функций pgv_set() и pgv_insert(). В противном случае произойдёт ошибка. Другим функциям передавать этот флаг не нужно.

SELECT pgv_insert('pack', 'var_record', row(123::int, 'text'::text), true);

SELECT pgv_insert('pack', 'var_record', row(456::int, 'another text'::text));
ERROR:  variable "var_record" already created as TRANSACTIONAL

SELECT pgv_delete('pack', 'var_record', 123::int);
    

Если вызовы функций pgv_free() или pgv_remove() откатываются, затрагиваемые транзакционные переменные восстанавливаются, в отличие от нетранзакционных, которые удаляются необратимо. Например:

SELECT pgv_set('pack', 'var_reg', 123);
SELECT pgv_set('pack', 'var_trans', 456, true);
BEGIN;
SELECT pgv_free();
ROLLBACK;
SELECT * FROM pgv_list();
 package |   name    | is_transactional 
---------+-----------+------------------
 pack    | var_trans | t

F.44.3. Функции

F.44.3.1. Скалярные переменные

Следующие функции поддерживают скалярные переменные:

ФункцияВозвращает
pgv_set(package text, name text, value anynonarray, is_transactional bool default false)void
pgv_get(package text, name text, var_type anynonarray, strict bool default true)anynonarray

Для функции pgv_get() вы должны сначала создать пакет и переменную, воспользовавшись функцией pgv_set(). Если указанный пакет или переменная не существуют, происходит ошибка:

SELECT pgv_get('vars', 'int1', NULL::int);
ERROR:  unrecognized package "vars"
SELECT pgv_get('vars', 'int1', NULL::int);
ERROR:  unrecognized variable "int1"

Функция pgv_get() проверяет тип переменной. Если заданный тип не соответствует типу переменной, выдаётся ошибка:

SELECT pgv_get('vars', 'int1', NULL::text);
ERROR:  variable "int1" requires "integer" value

F.44.3.2. Коллекции записей

Следующие функции поддерживают коллекции переменных типа запись.

ФункцияВозвращаетОписание
pgv_insert(package text, name text, r record, is_transactional bool default false)voidВставляет запись в переменную-коллекцию для заданного пакета. Если пакет или переменная не существуют, они создаются автоматически. Первый столбец записи r — первичный ключ. Если запись с таким же первичным ключом уже существует или эта переменная-коллекция имеет другую структуру, выдаётся ошибка.
pgv_update(package text, name text, r record)booleanИзменяет запись с соответствующим первичным ключом (он задаётся в первом столбце r). Возвращает true, если запись была найдена. Если эта переменная-коллекция имеет другую структуру, выдаётся ошибка.
pgv_delete(package text, name text, value anynonarray)booleanУдаляет запись с соответствующим первичным ключом (он задаётся в первом столбце r). Возвращает true, если запись была найдена, или false в противном случае.
pgv_select(package text, name text)set of recordsВозвращает записи из переменной-коллекции.
pgv_select(package text, name text, value anynonarray)recordВозвращает записи с соответствующими первичными ключами (первичный ключ задаётся в первом столбце r).
pgv_select(package text, name text, value anyarray)set of recordsВозвращает записи из переменных-коллекций с соответствующими первичными ключами (первичный ключ задаётся в первом столбце r).

Для использования функций pgv_update(), pgv_delete() и pgv_select() вы должны сначала создать пакет и переменную, воспользовавшись функцией pgv_insert(). Тип переменной и тип записи должны быть одинаковыми, иначе выдаётся ошибка.

F.44.3.3. Массивы

Следующие функции поддерживают переменные-массивы:

ФункцияВозвращает
pgv_set(package text, name text, value anyarray, is_transactional bool default false)void
pgv_get(package text, name text, var_type anyarray, strict bool default true)anyarray

Инструкции по использованию этих функций совпадают с приведёнными в Подразделе F.44.3.1 для скалярных переменных.

F.44.3.4. Общие коллекции

Следующие функции поддерживают общие переменные-коллекции:

ФункцияВозвращаетОписание

pgv_set_elem(package text, name text, key int, value anyelement, is_transactional bool default false)

pgv_set_elem(package text, name text, key text, value anyelement, is_transactional bool default false)

voidУстанавливает значение для элемента с ключом key переменной-коллекции name в пакете package. Если пакет или переменная не существуют, они создаются автоматически. Внутри одной коллекции разрешены только ключи одного типа. Аргумент key может быть типа int или text. Если элемент с указанным ключом уже существует, его значение изменяется на новое. Параметр is_transactional показывает, является ли новая переменная транзакционной, и по умолчанию имеет значение false. Таким образом, если переменная уже существует, она должна быть транзакционной/нетранзакционной, в зависимости от значения is_transactional, в противном случае возникает ошибка. Если коллекция уже существует и тип его значения не соответствует типу нового значения, также возникает ошибка.

pgv_get_elem(package text, name text, key int, val_type anyelement)

pgv_get_elem(package text, name text, key text, val_type anyelement)

anyelementВозвращает значение элемента с ключом key переменной-коллекции name в пакете package. Если в этой коллекции нет элемента с указанным ключом, возвращается NULL. Аргумент key может быть типа int или text. Если пакет или переменная не существует или указанная переменная не является коллекцией, возникает ошибка. Аргумент val_type необходим для правильного определения типа возвращаемого значения.

pgv_exists_elem(package text, name text, key int)

pgv_exists_elem(package text, name text, key text)

boolВозвращает true, если элемент с ключом key существует в переменной-коллекции name в пакете package, и >false в противном случае. Если коллекция или переменная не существует, также возвращается false. Аргумент key может быть типа int или text. Если указанная переменная не является коллекцией, возникает ошибка.

pgv_remove_elem(package text, name text, key int)

pgv_remove_elem(package text, name text, key text)

voidУдаляет элемент с ключом key переменной-коллекции name из пакета package. Если в этой коллекции нет элемента с указанным ключом, ничего не происходит. Аргумент key может быть типа int или text. Если пакет или переменная не существует или указанная переменная не является коллекции, возникает ошибка.

Важно

Коллекции, созданные функциями pgv_set_elem() и pgv_insert(), считаются несовместимыми.

F.44.3.5. Итераторы

Следующие функции предназначены для использования итераторов, чтобы передвигаться по переменным-коллекциям. Эти функции работают с коллекциями, созданными функциями pgv_set_elem() и pgv_insert().

ФункцияВозвращаетОписание
pgv_first(package text, name text, key_type anyelement)anyelementВозвращает первый ключ из переменной-коллекции. Коллекции сортируются по ключу в порядке возрастания. Параметр key_type необходим для определения возвращаемого значения. Если переданное значение name не является именем переменной-коллекции, возникает ошибка.
pgv_last(package text, name text, key_type anyelement)anyelementВозвращает последний ключ из переменной-коллекции. Коллекции сортируются по ключу в порядке возрастания. Параметр key_type необходим для определения возвращаемого значения. Если переданное значение name не является именем переменной-коллекции, возникает ошибка.
pgv_next(package text, name text, key anyelement)anyelementВозвращает следующий ключ из переменной-коллекции. Переданный ключ key может отсутствовать в коллекции. Возвращает NULL, если используется для последнего ключа в коллекции. Коллекции сортируются по ключу в порядке возрастания. Если переданное значение name не является именем переменной-коллекции, возникает ошибка.
pgv_prior(package text, name text, key anyelement)anyelementВозвращает предыдущий ключ из переменной-коллекции. Переданный ключ key может отсутствовать в коллекции. Возвращает NULL, если используется для первого ключа в коллекции. Коллекции сортируются по ключу в порядке возрастания. Если переданное значение name не является именем переменной-коллекции, возникает ошибка.
pgv_count(package text, name text)integerВозвращает количество элементов в коллекции. Если переданное значение name не является именем переменной-коллекции, возникает ошибка.

F.44.3.6. Функции разного назначения

ФункцияВозвращаетОписание
pgv_exists(package text, name text)boolВозвращает true, если существуют пакет и переменная, или false в противном случае.
pgv_exists(package text)boolВозвращает true, если существуют пакет и переменная, или false в противном случае.
pgv_remove(package text, name text)voidУдаляет переменную с заданным именем. Указанный пакет и переменная должны существовать, иначе выдаётся ошибка.
pgv_remove(package text)voidУдаляет заданный пакет и все его переменные. Указанный пакет должен существовать, иначе выдаётся ошибка.
pgv_free()voidУдаляет все пакеты и переменные.
pgv_list()table(package text, name text, is_transactional bool)Выводит список всех существующих переменных с именем соответствующего пакета и признаком того, что переменная транзакционная.
pgv_stats()table(package text, allocated_memory bigint)Возвращает список созданных пакетов и объём памяти, используемый переменными, в байтах. Если вы используете транзакционные переменные, в этот список также включаются все удалённые пакеты, которые могут быть восстановлены командой ROLLBACK. Эта функция поддерживается только в Postgres Pro версии 9.6 и новее.

F.44.3.7. Устаревшие функции

F.44.3.7.1. Целочисленные переменные

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_int(package text, name text, value int, is_transactional bool default false)void
pgv_get_int(package text, name text, strict bool default true)int
F.44.3.7.2. Текстовые переменные

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_text(package text, name text, value text, is_transactional bool default false)void
pgv_get_text(package text, name text, strict bool default true)text
F.44.3.7.3. Числовые переменные

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_numeric(package text, name text, value numeric, is_transactional bool default false)void
pgv_get_numeric(package text, name text, strict bool default true)numeric
F.44.3.7.4. Переменные даты/времени

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_timestamp(package text, name text, value timestamp, is_transactional bool default false)void
pgv_get_timestamp(package text, name text, strict bool default true)timestamp
F.44.3.7.5. Переменные даты/времени с часовым поясом

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_timestamptz(package text, name text, value timestamptz, is_transactional bool default false)void
pgv_get_timestamptz(package text, name text, strict bool default true)timestamptz
F.44.3.7.6. Переменные даты

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_date(package text, name text, value date, is_transactional bool default false)void
pgv_get_date(package text, name text, strict bool default true)date
F.44.3.7.7. Переменные Jsonb

Следующие функции считаются устаревшими. Используйте вместо них универсальные функции для скалярных переменных.

ФункцияВозвращает
pgv_set_jsonb(package text, name text, value jsonb, is_transactional bool default false)void
pgv_get_jsonb(package text, name text, strict bool default true)jsonb

F.44.4. Важные замечания

F.44.4.1. Правила сортировки в коллекциях

Коллекции хранятся в порядке возрастания. Для ключей типа text потребуется правило сортировки для определения порядка элементов. Если при вставке первого элемента коллекции правило сортировки не указано, используется правило сортировки по умолчанию. В противном случае используется указанное правило сортировки.

F.44.4.2. Курсоры для функций, возвращающих множества

Все функции, возвращающие множества, за исключением pgv_select(package,variable), фиксируют свои результаты при выполнении первой команды FETCH из курсора, и на них не влияют дальнейшие действия с данными.

Результаты pgv_select(package,variable) принимаются динамически, и на них влияют транзакции/изменения в коллекции. Для функции pgv_select(), вызываемой для транзакционной коллекции, снимок коллекции просматривается курсорами при выполнении первой команды FETCH, но с учётом изменений, которые были сделаны в этой транзакции и в зафиксированных подтранзакциях.

F.44.5. Примеры

Определение скалярных переменных с помощью функции pgv_set() и получение их значений с помощью pgv_get():

SELECT pgv_set('vars', 'int1', 101);
SELECT pgv_set('vars', 'int2', 102);
SELECT pgv_set('vars', 'text1', 'text variable'::text);

SELECT pgv_get('vars', 'int1', NULL::int);
 pgv_get
-------------
         101

SELECT pgv_get('vars', 'int2', NULL::int);
 pgv_get
-------------
         102

SELECT pgv_get('vars', 'text1', NULL::text);
 pgv_get
---------------
 text variable

В предположении, что создана следующая таблица tab, рассмотрите несколько примеров с переменными-записями:

CREATE TABLE tab (id int, t varchar);
INSERT INTO tab VALUES (0, 'str00'), (1, 'str11');

Для работы с переменными-записями можно использовать следующие функции:

SELECT pgv_insert('vars', 'r1', tab) FROM tab;

SELECT pgv_select('vars', 'r1');
 pgv_select
------------
 (1,str11)
 (0,str00)

SELECT pgv_select('vars', 'r1', 1);
 pgv_select
------------
 (1,str11)

SELECT pgv_select('vars', 'r1', 0);
 pgv_select
------------
 (0,str00)

SELECT pgv_select('vars', 'r1', ARRAY[1, 0]);
 pgv_select
------------
 (1,str11)
 (0,str00)

SELECT pgv_delete('vars', 'r1', 1);

SELECT pgv_select('vars', 'r1');
 pgv_select
------------
 (0,str00)

Проанализируйте поведение транзакционной переменной var_text при изменении до и после точки сохранения:

SELECT pgv_set('pack', 'var_text', 'before transaction block'::text, true);
BEGIN;
SELECT pgv_set('pack', 'var_text', 'before savepoint'::text, true);
SAVEPOINT sp1;
SELECT pgv_set('pack', 'var_text', 'savepoint sp1'::text, true);
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_text', 'savepoint sp2'::text, true);
RELEASE sp2;
SELECT pgv_get('pack', 'var_text', NULL::text);
    pgv_get
---------------
 savepoint sp2

ROLLBACK TO sp1;
SELECT pgv_get('pack', 'var_text', NULL::text);
     pgv_get
------------------
 before savepoint

ROLLBACK;
SELECT pgv_get('pack', 'var_text', NULL::text);
         pgv_get
--------------------------
 before transaction block

Если вы создадите транзакционную переменную после команд BEGIN или SAVEPOINT и затем произойдёт откат к предыдущему состоянию, эта переменная будет удалена:

BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_int', 122, true);
RELEASE SAVEPOINT sp2;
SELECT pgv_get('pack', 'var_int', NULL::int);
pgv_get
---------
     122

ROLLBACK TO sp1;
SELECT pgv_get('pack','var_int', NULL::int);
ERROR:  unrecognized variable "var_int"
COMMIT;

Просмотреть имеющиеся пакеты и переменные:

SELECT * FROM pgv_list() ORDER BY package, name;
 package |   name   | is_transactional 
---------+----------+------------------
 pack    | var_text | t
 vars    | int1     | f
 vars    | int2     | f
 vars    | r1       | f
 vars    | text1    | f

Получение объёма памяти, занятой переменными, в байтах:

SELECT * FROM pgv_stats() ORDER BY package;
 package | allocated_memory 
---------+------------------
 pack    |            16384
 vars    |            32768

Удаление избранных переменных или пакетов:

SELECT pgv_remove('vars', 'int1');
SELECT pgv_remove('vars');

Удаление всех пакетов и переменных:

SELECT pgv_free();

В этих примерах показано использование переменных-коллекций и функций итераторов:

sql
SELECT pgv_set_elem('pack', 'var', 1, 1);
SELECT pgv_set_elem('pack', 'var', 5, 5);
SELECT pgv_set_elem('pack', 'var', 10, 10);

SELECT pgv_first('pack', 'var', NULL::int);
 pgv_first
-----------
         1

SELECT pgv_last('pack', 'var', NULL::int);
 pgv_last
----------
       10

SELECT pgv_next('pack', 'var', pgv_first('pack', 'var', NULL::int));
 pgv_next
----------
        5

SELECT pgv_prior('pack', 'var', pgv_last('pack', 'var', NULL::int));
 pgv_prior
-----------
         5

SELECT pgv_prior('pack', 'var', pgv_first('pack', 'var', NULL::int));
 pgv_prior
-----------

SELECT pgv_next('pack', 'var', pgv_last('pack', 'var', NULL::int));
 pgv_prior
-----------

SELECT pgv_next('pack', 'var', 3);
 pgv_next
----------
        5

SELECT pgv_prior('pack', 'var', 3);
 pgv_prior
-----------
         1

SELECT pgv_get_elem('pack', 'var', pgv_last('pack', 'var', NULL::int), NULL::int);
 pgv_get_elem
--------------
           10

SELECT pgv_remove_elem('pack', 'var', pgv_last('pack', 'var', NULL::int));
 pgv_remove_elem
-----------------


SELECT pgv_get_elem('pack', 'var', pgv_last('pack', 'var', NULL::int), NULL::int);
 pgv_get_elem
--------------
           5
(1 row)

Эти примеры показывают, как правила сортировки влияют на порядок элементов в коллекции, а также как перебирать всю коллекцию с помощью циклов PL/pgSQL:

sql
SELECT pgv_set_elem('pack', 'var1', 'а' COLLATE "ru_RU", 'а'::text);
SELECT pgv_set_elem('pack', 'var1', 'д' COLLATE "ru_RU", 'д'::text);
SELECT pgv_set_elem('pack', 'var1', 'е' COLLATE "ru_RU", 'е'::text);
SELECT pgv_set_elem('pack', 'var1', 'ё' COLLATE "ru_RU", 'ё'::text);
SELECT pgv_set_elem('pack', 'var1', 'ж' COLLATE "ru_RU", 'ж'::text);
SELECT pgv_set_elem('pack', 'var1', 'я' COLLATE "ru_RU", 'я'::text);
DO
$$
DECLARE
        iter text;
BEGIN
        iter := pgv_first('pack', 'var1', NULL::text);
        WHILE iter IS NOT NULL LOOP
          RAISE NOTICE '%', pgv_get_elem('pack', 'var1', iter, NULL::text);
          iter := pgv_next('pack', 'var1', iter);
        END LOOP;
END;
$$;
NOTICE:  а
NOTICE:  д
NOTICE:  е
NOTICE:  ё
NOTICE:  ж
NOTICE:  я

SELECT pgv_set_elem('pack', 'var2', 'а' COLLATE "C", 'а'::text);
SELECT pgv_set_elem('pack', 'var2', 'д' COLLATE "C", 'д'::text);
SELECT pgv_set_elem('pack', 'var2', 'е' COLLATE "C", 'е'::text);
SELECT pgv_set_elem('pack', 'var2', 'ё' COLLATE "C", 'ё'::text);
SELECT pgv_set_elem('pack', 'var2', 'ж' COLLATE "C", 'ж'::text);
SELECT pgv_set_elem('pack', 'var2', 'я' COLLATE "C", 'я'::text);
DO
$$
DECLARE
        iter text;
BEGIN
        iter := pgv_first('pack', 'var2', NULL::text);
        WHILE iter IS NOT NULL LOOP
          RAISE NOTICE '%', pgv_get_elem('pack', 'var2', iter, NULL::text);
          iter := pgv_next('pack', 'var2', iter);
        END LOOP;
END;
$$;
NOTICE:  а
NOTICE:  д
NOTICE:  е
NOTICE:  ж
NOTICE:  я
NOTICE:  ё

F.44.6. Авторы

Postgres Professional, Москва, Россия