## F.49. 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.

### Note

This extension cannot be used together with autonomous transactions or while built-in connection pooling is enabled.

### F.49.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.49.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.49.3.1 describes functions for scalar variables.

- Section F.49.3.2 describes functions for record variables.

- Section F.49.3.3 describes functions for array variables.

- Section F.49.3.4 lists functions you can use to manage all variables in your current session.

For detailed usage examples, see Section F.49.4.

#### F.49.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.49.3. Functions

#### F.49.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.49.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.49.3.3. Records

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.49.3.1 for scalar variables.

#### F.49.3.4. 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, allocated_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.49.3.5. Deprecated Functions

##### F.49.3.5.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.49.3.5.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.49.3.5.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.49.3.5.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.49.3.5.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.49.3.5.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.49.3.5.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.49.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.49.5. Authors

Postgres Professional, Moscow, Russia