## F.51. pg_variables

### F.51.1. Introduction

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

The `pg_variables` module does not support transactions and savepoints. For example:

    postgres=# SELECT pgv_set('vars', 'int1', 101);
    BEGIN;
    postgres=# SELECT pgv_set('vars', 'int2', 102);
    ROLLBACK;

    postgres=# SELECT * FROM pgv_list() ORDER BY package, name;
     package | name
    ---------+------
     vars    | int1
     vars    | int2
    (2 rows)

### F.51.2. 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.51.3. Installation

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

    CREATE EXTENSION pg_variables;

### F.51.4. Functions

The module supports scalar and record types. The functions provided by the `pg_variables` module for the supported types are shown in the tables below.

#### F.51.4.1. Scalar Variables

The following functions support scalar variables:

Function | Returns
--- | ---
`pgv_set(package text, name text, value anynonarray)` | `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:

    postgres=# SELECT pgv_get('vars', 'int1');
    ERROR:  unrecognized package "vars"

    postgres=# 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.51.4.2. Records

The following functions support collections of record variables:

Function | Returns | Description
--- | --- | ---
`pgv_insert(package text, name text, r record)` | `void` | Inserts a record into the 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.51.4.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 corresponding name. The specified package and variable must exist; otherwise, an error is raised.
`pgv_remove(package text)` | `void` | Removes the package and all package variables with the corresponding name. 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)` | Returns set of records of assigned packages and variables.
`pgv_stats()` | `table(package text, used_memory bigint)` | Returns the list of assigned packages and the amount of memory used by variables, in bytes. This function only supports Postgres Pro 9.6 or higher.

#### F.51.4.4. Integer Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_int(package text, name text, value int)` | `void`
`pgv_get_int(package text, name text, strict bool default true)` | `int`

#### F.51.4.5. Text Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_text(package text, name text, value text)` | `void`
`pgv_get_text(package text, name text, strict bool default true)` | `text`

#### F.51.4.6. Numeric Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_numeric(package text, name text, value numeric)` | `void`
`pgv_get_numeric(package text, name text, strict bool default true)` | `numeric`

#### F.51.4.7. Timestamp Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_timestamp(package text, name text, value timestamp)` | `void`
`pgv_get_timestamp(package text, name text, strict bool default true)` | `timestamp`

#### F.51.4.8. Timestamp with timezone Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_timestamptz(package text, name text, value timestamptz)` | `void`
`pgv_get_timestamptz(package text, name text, strict bool default true)` | `timestamptz`

#### F.51.4.9. Date Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_date(package text, name text, value date)` | `void`
`pgv_get_date(package text, name text, strict bool default true)` | `date`

#### F.51.4.10. Jsonb Variables (Deprecated)

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

Function | Returns
--- | ---
`pgv_set_jsonb(package text, name text, value jsonb)` | `void`
`pgv_get_jsonb(package text, name text, strict bool default true)` | `jsonb`

### F.51.5. Examples

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

    postgres=# SELECT pgv_set('vars', 'int1', 101);
    postgres=# SELECT pgv_set('vars', 'int2', 102);

    postgres=# SELECT pgv_get('vars', 'int1', NULL::int);
     pgv_get
    -------------
             101
    (1 row)

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

Let's assume we have the `tab` table:

    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:

    postgres=# SELECT pgv_insert('vars', 'r1', tab) FROM tab;

    postgres=# SELECT pgv_select('vars', 'r1');
     pgv_select
    ------------
     (1,str11)
     (0,str00)
    (2 rows)

    postgres=# pgv_select('vars', 'r1', 1);
     pgv_select
    ------------
     (1,str11)
    (1 row)

    postgres=# SELECT pgv_select('vars', 'r1', 0);
     pgv_select
    ------------
     (0,str00)
    (1 row)

    postgres=# SELECT pgv_select('vars', 'r1', ARRAY[1, 0]);
     pgv_select
    ------------
     (1,str11)
     (0,str00)
    (2 rows)

    postgres=# SELECT pgv_delete('vars', 'r1', 1);

    postgres=# SELECT pgv_select('vars', 'r1');
     pgv_select
    ------------
     (0,str00)
    (1 row)

List the available packages and variables:

    postgres=# SELECT * FROM pgv_list() ORDER BY package, name;
     package | name
    ---------+------
     vars    | int1
     vars    | int2
     vars    | r1
    (3 rows)

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

    postgres=# SELECT * FROM pgv_stats() ORDER BY package;
     package | used_memory
    ---------+-------------
     vars    |       16736
    (1 row)

Delete the specified variables or packages:

    postgres=# SELECT pgv_remove('vars', 'int1');
    postgres=# SELECT pgv_remove('vars');

Delete all packages and variables:

    postgres=# SELECT pgv_free();

### F.51.6. Authors

Postgres Professional, Moscow, Russia