F.44. pg_variables

The pg_variables module provides functions for working with variables of various types. The created variables are only available in the current user session.

F.44.1. Installation

The pg_variables extension is included into Postgres Pro. Once you have Postgres Pro installed, you must execute the CREATE EXTENSION command to enable pg_variables, as follows:

CREATE EXTENSION pg_variables;

F.44.2. Usage

The pg_variables module provides several functions for creating, reading, and managing variables of scalar and record types. See the following sections for function descriptions and syntax:

For detailed usage examples, see Section F.44.4.

F.44.2.1. Using Transactional Variables

By default, the created variables are non-transactional. Once successfully set, a variable exists for the whole session, regardless of rollbacks, if any. For example:

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

If you would like to use variables that support transactions and savepoints, pass the optional is_transactional flag as the last parameter when creating this variable:

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

You must use the is_transactional flag every time you change the value of a transactional variable using pgv_set() or pgv_insert() functions. Otherwise, an error occurs. Other functions do not require this flag.

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

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', row(456::int, 'another text'::text));
 pgv_delete
    

If the pgv_free() or pgv_remove() function calls are rolled back, the affected transactional variables will be restored, unlike non-transactional variables, which are removed permanently. For example:

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. Functions

F.44.3.1. Scalar Variables

The following functions support scalar variables:

Function Returns
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

To use the pgv_get() function, you must first create a package and a variable using the pgv_set() function. If the specified package or variable does not exist, an error occurs:

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

pgv_get() function checks the variable type. If the specified type does not match the type of the variable, an error is raised:

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

F.44.3.2. Records

The following functions support collections of record variables:

Function Returns Description
pgv_insert(package text, name text, r record, is_transactional bool default false)void Inserts a record into a variable collection for the specified package. If the package or variable does not exist, it is created automatically. The first column of r is the primary key. If a record with the same primary key already exists or this variable collection has another structure, an error is raised.
pgv_update(package text, name text, r record)boolean Updates a record with the corresponding primary key (the first column of r is the primary key). Returns true if the record was found. If this variable collection has another structure, an error is raised.
pgv_delete(package text, name text, value anynonarray)boolean Deletes a record with the corresponding primary key (the first column of r is the primary key). Returns true if the record was found.
pgv_select(package text, name text)set of records Returns the variable collection records.
pgv_select(package text, name text, value anynonarray)record Returns the record with the corresponding primary key (the first column of r is a primary key).
pgv_select(package text, name text, value anyarray)set of records Returns the variable collection records with the corresponding primary keys (the first column of r is a primary key).

To use pgv_update(), pgv_delete() and pgv_select() functions, you must first create a package and a variable using the pgv_insert() function. The variable type and the record type must be the same; otherwise, an error occurs.

F.44.3.3. Miscellaneous Functions

Function Returns Description
pgv_exists(package text, name text)bool Returns true if the specified package and variable exist.
pgv_exists(package text)bool Returns true if the specified package exists.
pgv_remove(package text, name text)void Removes the variable with the specified name. The specified package and variable must exist; otherwise, an error is raised.
pgv_remove(package text)void Removes the specified package and all the corresponding variables. The specified package must exist; otherwise, an error is raised.
pgv_free()void Removes all packages and variables.
pgv_list()table(package text, name text, is_transactional bool) Displays all the available variables and the corresponding packages, as well as whether each variable is transactional.
pgv_stats()table(package text, used_memory bigint) Returns the list of assigned packages and the amount of memory used by variables, in bytes. If you are using transactional variables, this list also includes all deleted packages that still may be restored by a ROLLBACK. This function only supports Postgres Pro 9.6 or higher.

F.44.3.4. Deprecated Functions

F.44.3.4.1. Integer Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.2. Text Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.3. Numeric Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.4. Timestamp Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.5. Timestamp with timezone Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.6. Date Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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.4.7. Jsonb Variables

The following functions are deprecated. Use generic functions for scalar variables instead.

Function Returns
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. Examples

Define scalar variables using the pgv_set() function, and then return their values using the pgv_get() function:

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
(1 row)

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

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

Let's assume we have the tab table and examine several examples of using record variables:

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

You can use the following functions to work with record variables:

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)

Consider the behavior of a transactional variable var_text when changed before and after savepoints:

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

If you create a variable after BEGIN or SAVEPOINT statements and than rollback to the previous state, the transactional variable is removed:

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;

List the available packages and variables:

SELECT * FROM pgv_list() ORDER BY package, name;
 package | name  | is transactional
---------+-------+-----------------
 vars    | int1  | f
 vars    | int2  | f
 vars    | r1    | f

Get the amount of memory used by variables, in bytes:

SELECT * FROM pgv_stats() ORDER BY package;
 package | allocated_memory
---------+-----------------
 vars    |           16736
(1 row)

Delete the specified variables or packages:

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

Delete all packages and variables:

SELECT pgv_free();

F.44.5. Authors

Postgres Professional, Moscow, Russia