F.54. pg_variables

F.54. pg_variables
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

F.54.1. About pg_variables #

Version: 1.2

This module is distributed under a PostgreSQL license.

F.54.2. Introduction #

pg_variables is an extension that provides functions to work with with variables of various types. The created variables exist only within the current user session.

By default, variables are created non-transactionally (thus they do not affect BEGIN, COMMIT or ROLLBACK). This can be controlled via the is_transactional argument in the pgv_set() function:

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

But if the variable is created with the is_transactional flag:

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 can group variables into packages. This is done in order to have variables with different names or quickly delete them. If the package becomes empty, it is automatically removed.

F.54.3. Installation #

A typical installation:

CREATE EXTENSION pg_variables;

F.54.4. Functions #

The functions available in the pg_variables extension are described in the table below.

To use the pgv_get() function, the package and variable must exist. It is required to set a variable with the pgv_set() function in order to use the pgv_get() function.

If the package does not exist, you will receive the following error:

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

If the variable does not exist, you will get the following error:

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

The pgv_get() function checks the type of a variable. If the type of the variable does not match the type in the function, you will get the following error:

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

F.54.5. Scalar variables functions #

Function	Result
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`

F.54.6. Array variables functions #

Function	Result
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`

pgv_set arguments:

package - the name of the package, it should be created if it doesn't exist.
name - the name of the variable, it will be created if it doesn't exist. pgv_set fails if the variable already exists and its transactionality does not match the is_transactional argument.
value - the new value of the variable. pgv_set fails if the variable already exists and its type does not match the type of the new value.
is_transactional - Transactionality of the newly created variable, false by default.

pgv_get arguments:

package - is the name of an existing package. If the package does not exist, the result depends on the strict argument: if it is false, then pgv_get returns NULL, otherwise it fails.
name - the name of an existing variable. If the variable does not exist, the result depends on the strict argument: if it is false, then pgv_get returns NULL, otherwise it fails.
var_type - the type of the existing variable. It must be passed in order to get the correct return type.
strict - pass false if pgv_get shouldn't throw an error if the variable or package hasn't been created before, default is true.

F.54.7. Record variable functions #

The module provides the following functions for working with collections of record types.

To use the pgv_update(), pgv_delete(), and pgv_select() functions, the required package and variable must exist. Otherwise, an error will be raised. To use these functions, you must set the variable using the pgv_insert() function.

The pgv_update(), pgv_delete(), and pgv_select() functions check the type of a variable. If the type of the variable does not record the type, an error will be raised.

Table F.29. Record variable functions

Function	Returns	Description
pgv_insert(package text, name text, r record, is_transactional bool default false)	void	Inserts a record to the variable collection. If package and variable do not exists they will be created. The first column of r will be a primary key. If exists a record with the same primary key the error will be raised. If this variable collection has other structure the error will be raised.
pgv_update(package text, name text, r record)	boolean	Updates a record with the corresponding primary key (the first column of r is a primary key). Returns true if a record was found. If this variable collection has other structure the error will be raised.
pgv_delete(package text, name text, value anynonarray)	boolean	Deletes a record with the corresponding primary key (the first column of r is a primary key). Returns true if a record was found.
pgv_select(package text, name text)	set of record	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 record	Returns the variable collection records with the corresponding primary keys (the first column of r is a primary key).

F.54.7.1. Miscellaneous functions #

Table F.30. Miscellaneous functions

Function	Returns	Description
pgv_exists(package text, name text)	bool	Returns true if package and variable exists.
pgv_exists(package text)	bool	Returns true if package exists.
pgv_remove(package text, name text)	void	Removes the variable with the corresponding name. Required package and variable must exists, otherwise the error will be raised.
pgv_remove(package text)	void	Removes the package and all package variables with the corresponding name. Required package must exists, otherwise the error will be raised.
pgv_free()	void	Removes all packages and variables.
pgv_list()	table(package text, name text, is_transactional bool)	Returns set of records of assigned packages and variables.
pgv_stats()	table(package text, allocated_memory bigint)	Returns list of assigned packages and used memory in bytes.

F.54.8. Examples #

It is easy to use functions to work with scalar and array variables:

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

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

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

SELECT pgv_set('vars', 'arr1', '{101,102}'::int[]);

SELECT pgv_get('vars', 'arr1', NULL::int[]);
  pgv_get
-----------
 {101,102}

Suppose we have a tab table:

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

Then you can use 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)

You can list packages and variables:

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

And we get the used memory in bytes:

SELECT * FROM pgv_stats() order by package;
 package | allocated_memory
---------+------------------
 vars    |            49152

You can remove variables or entire packages:

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

You can remove all packages and variables:

SELECT pgv_free();

If you need transactional and savepoint-enabled variables, you must add the is_transactional = true flag as the last argument to pgv_set() or pgv_insert(). The following use cases describe the behavior of transactional variables:

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 transactional variable after a BEGIN or SAVEPOINT statement and then revert to the previous state, the variable will not exist:

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;

You can undo the deletion of a transactional variable with ROLLBACK, but if you delete the entire package, all regular variables will be deleted permanently:

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

-- Memory is allocated yet
SELECT * FROM pgv_stats();
 package | allocated_memory
---------+------------------
 pack    |            24576

ROLLBACK;
SELECT * FROM pgv_list();
 package |   name    | is_transactional
---------+-----------+------------------
 pack    | var_trans | t

If you created a transactional variable once, you should use the is_transactional flag every time you want to change the variable's value using the pgv_set() and pgv_insert() deprecated setter functions (i.e. pgv_set_int()). If you try to change this setting, you will receive an error message:

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

The pgv_update() and pgv_delete() functions do not require this flag.

F.54. pg_variables#

F.54. pg_variables #