Postgres Pro Enterprise : Documentation: 10: F.50. pg

F.50. pg_variables
Prev	Up	Appendix F. Additional Supplied Modules	Home	Next

F.50. pg_variables

F.50.1. Installation
F.50.2. Usage
F.50.3. Functions
F.50.4. Examples
F.50.5. 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 together with autonomous transactions.

F.50.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.50.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.50.3.1 describes functions for scalar variables.
Section F.50.3.2 describes functions for record variables.
Section F.50.3.3 describes functions for array variables.
Section F.50.3.4 lists functions you can use to manage all variables in your current session.

For detailed usage examples, see Section F.50.4.

F.50.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.50.3. Functions

F.50.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.50.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.50.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.50.3.1 for scalar variables.