vars | int1 | f

vars | int2 | f

(2 rows)

But if variable created with flag**is_transactional**:

ROLLBACK TO sp1;

COMMIT;

SELECT pgv_get('vars','trans_int',NULL::int);

pgv_get

(1 row)

`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,used_memory bigint)` | Returns list of assigned packages and used memory in bytes.

`pgv_stats()` |`table(package text,allocated_memory bigint)` | Returns list of assigned packages and used memory in bytes.

Note that**pgv_stats()** works only with the PostgreSQL 9.6 and newer.

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_int

(1 row)

SELECT pgv_get('vars','int2',NULL::int);

pgv_get_int

(1 row)

SELECTSELECT pgv_get('vars','text1',NULL::text);

pgv_get

text variable

Let's assume we have a**tab** table:

(1,str11)

(0,str00)

(2 rows)

SELECT pgv_select('vars','r1',1);

pgv_select

(1,str11)

(1 row)

SELECT pgv_select('vars','r1',0);

pgv_select

(0,str00)

(1 row)

SELECT pgv_select('vars','r1', ARRAY[1,0]);

pgv_select

(1,str11)

(0,str00)

(2 rows)

SELECT pgv_delete('vars','r1',1);

SELECT pgv_select('vars','r1');

pgv_select

(0,str00)

(1 row)

You can list packages and variables:

SELECT*FROM pgv_list()order by package, name;

package | name | is_transactional

vars | int1 | f

vars | int2 | f

vars | r1 | f

(3 rows)

package | name | is_transactional

vars | int1 | f

vars | r1 | f

vars | text1 | f

And get used memory in bytes:

SELECT*FROM pgv_stats()order by package;

package | used_memory

vars |16736

(1 row)

package | allocated_memory

vars |32768

You can delete variables or whole packages:

add flag`is_transactional = true` as the last argument in functions`pgv_set()`

or`pgv_insert()`.

Following use cases describe behavior of transactional variables:

SELECT pgv_set('pack','var_text','before transaction block'::text, true);

BEGIN;

pgv_get

before savepoint

(1 row)

ROLLBACK;

SELECT pgv_get('pack','var_text',NULL::text);

pgv_get

before transaction block

If you create variable after`BEGIN` or`SAVEPOINT` statements and than rollback

to previous state - variable will not be exist:

BEGIN;

SAVEPOINT sp1;

pgv_get

(1 row)

ROLLBACK TO sp1;

SELECT pgv_get('pack','var_int',NULL::int);

ERROR: unrecognized variable"var_int"

COMMIT;

You can undo removal of a transactional variable by`ROLLBACK`, but if you remove

a whole package, all regular variables will be removed permanently:

SELECT pgv_set('pack','var_reg',123);

SELECT pgv_set('pack','var_trans',456, true);

(1 row)

If you created transactional variable once, you should use flag`is_transactional`

every time when you want to change variable value by functions`pgv_set()`,

`pgv_insert()` and deprecated setters (i.e.`pgv_set_int()`). If you try to

change this option, you'll get an error:

SELECT pgv_insert('pack','var_record', row(123::int,'text'::text), true);

pgv_insert

(1 row)

SELECT pgv_insert('pack','var_record', row(456::int,'another text'::text));

ERROR: variable"var_record" already createdas TRANSACTIONAL

Functions`pgv_update()` and`pgv_delete()` do not require this flag.