Postgres Pro Enterprise : Documentation: 16: F.60. pg_variables — functions for working with variables of various types : Postgres Professional

F.60. pg_variables — functions for working with variables of various types
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-ent-16-contrib`	Home	Next

F.60. pg_variables — functions for working with variables of various types #

F.60.1. Installation
F.60.2. Usage
F.60.3. Functions
F.60.4. Important Notes
F.60.5. Examples
F.60.6. Authors

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

Note

This extension cannot be used while built-in connection pooling is enabled.

F.60.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.60.2. Usage #

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

Section F.60.3.1 describes functions for scalar variables.
Section F.60.3.2 describes functions for collections of record variables.
Section F.60.3.3 describes functions for array variables.
Section F.60.3.4 describes functions for general collection variables.
Section F.60.3.5 describes functions for using iterators to traverse collection variables.
Section F.60.3.6 lists functions you can use to manage all variables in your current session.

For detailed usage examples, see Section F.60.5.

F.60.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);

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

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

F.60.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', NULL::int);
ERROR:  unrecognized package "vars"

SELECT pgv_get('vars', 'int1', NULL::int);
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.60.3.2. Collections of 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 collection variable 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 collection variable has a different 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 collection variable has a different 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 and `false` otherwise.
`pgv_select(package text, name text)`	`set of records`	Returns the collection variable 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 collection variable 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.60.3.3. Arrays #

The following functions support array variables:

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

Usage instructions for these functions are the same as those provided in Section F.60.3.1 for scalar variables.

F.60.3.4. General Collections #

The following functions support general collection variables:

Function	Returns	Description
`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`	Sets a value for the element with the key `key` of the collection variable `name` in the package `package`. If the package or variable does not exist, it is created automatically. Inside one collection, only keys of the same type are allowed. The `key` argument can have either `int` or `text` type. If an element with the specified key already exists, its value is set to the new one. `is_transactional` shows whether the new variable is transactional and equals `false` by default. So if the variable already exists, it must be transactional/non-transactional as indicated by `is_transactional`, otherwise an error is raised. If the collection already exists and its value type does not match the type of the new value, an error is also raised.
`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`	Returns the value of the element with the key `key` of the collection variable `name` in the package `package`. If there is no element with the specified key in this collection, returns NULL. The `key` argument can have either `int` or `text` type. If the package or variable does not exist, an error is raised. If the specified variable is not a collection, an error is also raised. The `val_type` argument is required to properly determine the return type.
`pgv_exists_elem(package text, name text, key int)` `pgv_exists_elem(package text, name text, key text)`	`bool`	Returns `true` if an element with the key `key` exists in the collection variable `name` in the package `package` and `false` otherwise. If the package or variable does not exist, also returns `false`. The `key` argument can have either `int` or `text` type. If the specified variable is not a collection, an error is raised.
`pgv_remove_elem(package text, name text, key int)` `pgv_remove_elem(package text, name text, key text)`	`void`	Removes the element with the key `key` from the collection variable `name` in the package `package`. If there is no element with the specified key in this collection, does nothing. The `key` argument can have either `int` or `text` type. If the package or variable does not exist, an error is raised. If the specified variable is not a collection, an error is also raised.

Important

Collections initialized with pgv_set_elem() and with pgv_insert() are not considered compatible.

F.60.3.5. Iterators #

The following functions are provided to use iterators to traverse collection variables. These functions work with collections initialized with both pgv_set_elem() and pgv_insert().

Function	Returns	Description
`pgv_first(package text, name text, key_type anyelement)`	`anyelement`	Returns the first key from the collection variable. Collections are sorted by the key in ascending order. `key_type` is required to determine the return value. If the `name` passed is the name of a non-collection variable, an error is raised.
`pgv_last(package text, name text, key_type anyelement)`	`anyelement`	Returns the last key from the collection variable. Collections are sorted by the key in ascending order. `key_type` is required to determine the return value. If the `name` passed is the name of a non-collection variable, an error is raised.
`pgv_next(package text, name text, key anyelement)`	`anyelement`	Returns the next key from the collection variable. The `key` passed may not exist in the collection. Returns NULL if used for the last key in the collection. Collections are sorted by the key in ascending order. If the `name` passed is the name of a non-collection variable, an error is raised.
`pgv_prior(package text, name text, key anyelement)`	`anyelement`	Returns the previous key from the collection variable. The `key` passed may not exist in the collection. Returns NULL if used for the first key in the collection. Collections are sorted by the key in ascending order. If the `name` passed is the name of a non-collection variable, an error is raised.
`pgv_count(package text, name text)`	`integer`	Returns the number of elements in the collection. If the `name` passed is the name of a non-collection variable, an error is raised.