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

CREATE EXTENSION pg_variables;

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

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

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

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

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);
    

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

Определение скалярных переменных с помощью функции 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:  ё

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