postgrespro/pg_variablesPublic

NotificationsYou must be signed in to change notification settings
Fork2
Star4

Session wide variables for PostgreSQL

License

View license

4 stars 2 forks Branches Tags Activity

Star

Notifications

You must be signed in to change notification settings

Branches Tags

Folders and files

Name		Name	Last commit message	Last commit date
Latest commit History 200 Commits
expected		expected
sql		sql
.dockerignore		.dockerignore
.gitignore		.gitignore
.travis.yml		.travis.yml
Dockerfile.tmpl		Dockerfile.tmpl
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
docker-compose.yml		docker-compose.yml
mk_dockerfile.sh		mk_dockerfile.sh
pg_variables--1.0--1.1.sql		pg_variables--1.0--1.1.sql
pg_variables--1.0.sql		pg_variables--1.0.sql
pg_variables--1.1--1.2.sql		pg_variables--1.1--1.2.sql
pg_variables.c		pg_variables.c
pg_variables.control		pg_variables.control
pg_variables.h		pg_variables.h
pg_variables_record.c		pg_variables_record.c
run_tests.sh		run_tests.sh
valgrind.supp		valgrind.supp

Repository files navigation

pg_variables - session variables with various types

Introduction

Thepg_variables module provides functions to work with variables of varioustypes. Created variables live only in the current user session.By default, created variables are not transactional (i.e. they are not affectedbyBEGIN,COMMIT orROLLBACK statements). This, however, is customizableby argumentis_transactional ofpgv_set():

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

But if variable created with flagis_transactional:

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 can aggregate variables into packages. This is done to be able to havevariables with different names or to quickly remove the whole batch ofvariables. If the package becomes empty, it is automatically deleted.

License

This module available under thelicense similar toPostgreSQL.

Installation

Typical installation procedure may look like this:

$ cd pg_variables$ make USE_PGXS=1$ sudo make USE_PGXS=1 install$ make USE_PGXS=1 installcheck$ psql DB -c "CREATE EXTENSION pg_variables;"

Module functions

The functions provided by thepg_variables module are shown in the tablesbelow.

To usepgv_get() function required package and variable must exists. It isnecessary to set variable withpgv_set() function to usepgv_get()function.

If a package does not exists you will get the following error:

SELECT pgv_get('vars','int1',NULL::int);ERROR:  unrecognized package"vars"

If a variable does not exists you will get the following error:

SELECT pgv_get('vars','int1',NULL::int);ERROR:  unrecognized variable"int1"

pgv_get() function check the variable type. If the variable type does notmatch with the function type the error will be raised:

SELECT pgv_get('vars','int1',NULL::text);ERROR:  variable"int1" requires"integer" value

Scalar variables functions

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`

Array variables functions

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`

pgv_set arguments:

package - name of the package, it will be created if it doesn't exist.
name - name of the variable, it will be created if it doesn't exist.pgv_set fails if the variable already exists and its transactionality doesn'tmatchis_transactional argument.
value - new value for the variable.pgv_set fails if the variable alreadyexists and its type doesn't match new value's type.
is_transactional - transactionality of the newly created variable, bydefault it is false.

pgv_get arguments:

package - name of the existing package. If the package doesn't exist resultdepends onstrict argument: if it is false thenpgv_get returns NULLotherwise it fails.
name - name of the the existing variable. If the variable doesn't existresult depends onstrict argument: if it is false thenpgv_get returns NULLotherwise it fails.
var_type - type of the existing variable. It is necessary to pass it to getcorrect return type.
strict - pass false ifpgv_get shouldn't raise an error if a variable or apackage didn't created before, by default it is true.

Deprecated scalar variables functions

Integer variables

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`

Text variables

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`

Numeric variables

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`

Timestamp variables

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`

Timestamp with timezone variables

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`

Date variables

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`

Jsonb variables

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`

Record variables functions

The following functions are provided by the module to work with collections ofrecord types.

To usepgv_update(),pgv_delete() andpgv_select() functionsrequired package and variable must exists. Otherwise the error will be raised.It is necessary to set variable withpgv_insert() function to use thesefunctions.

pgv_update(),pgv_delete() andpgv_select() functions check thevariable type. If the variable type does notrecord type the error will beraised.

Function	Returns	Description
`pgv_insert(package text, name text, r record, is_transactional bool default false)`	`void`	Inserts a record to the variable collection. If package and variable do not exists they will be created. The first column ofr will be a primary key. If exists a record with the same primary key the error will be raised. If this variable collection has other structure the error will be raised.
`pgv_update(package text, name text, r record)`	`boolean`	Updates a record with the corresponding primary key (the first column ofr is a primary key). Returnstrue if a record was found. If this variable collection has other structure the error will be raised.
`pgv_delete(package text, name text, value anynonarray)`	`boolean`	Deletes a record with the corresponding primary key (the first column ofr is a primary key). Returnstrue if a record was found.
`pgv_select(package text, name text)`	`set of record`	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 ofr is a primary key).
`pgv_select(package text, name text, value anyarray)`	`set of record`	Returns the variable collection records with the corresponding primary keys (the first column ofr is a primary key).

Miscellaneous functions

Function	Returns	Description
`pgv_exists(package text, name text)`	`bool`	Returnstrue if package and variable exists.
`pgv_exists(package text)`	`bool`	Returnstrue if package exists.
`pgv_remove(package text, name text)`	`void`	Removes the variable with the corresponding name. Required package and variable must exists, otherwise the error will be raised.
`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, allocated_memory bigint)`	Returns list of assigned packages and used memory in bytes.

Note thatpgv_stats() works only with the PostgreSQL 9.6 and newer.

Examples

It is easy to use functions to work with scalar and array variables:

SELECT pgv_set('vars','int1',101);SELECT pgv_set('vars','text1','text variable'::text);SELECT pgv_get('vars','int1',NULL::int); pgv_get_int-------------101SELECTSELECT pgv_get('vars','text1',NULL::text);    pgv_get---------------text variableSELECT pgv_set('vars','arr1','{101,102}'::int[]);SELECT pgv_get('vars','arr1',NULL::int[]);  pgv_get----------- {101,102}

Let's assume we have atab table:

CREATETABLEtab (idint, tvarchar);INSERT INTO tabVALUES (0,'str00'), (1,'str11');

Then you can use 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)

You can list packages and variables:

SELECT*FROM pgv_list()order by package, name; package | name  | is_transactional---------+-------+------------------ vars    | arr1  | f vars    | int1  | f vars    | r1    | f vars    | text1 | f

And get used memory in bytes:

SELECT*FROM pgv_stats()order by package; package | allocated_memory---------+------------------ vars    |49152

You can delete variables or whole packages:

SELECT pgv_remove('vars','int1');SELECT pgv_remove('vars');

You can delete all packages and variables:

SELECT pgv_free();

If you want variables with support of transactions and savepoints, you shouldadd flagis_transactional = true as the last argument in functionspgv_set()orpgv_insert().Following use cases describe behavior of transactional variables:

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 sp2ROLLBACK TO sp1;SELECT pgv_get('pack','var_text',NULL::text);     pgv_get------------------ before savepointROLLBACK;SELECT pgv_get('pack','var_text',NULL::text);         pgv_get-------------------------- before transaction block

If you create a transactional variable afterBEGIN orSAVEPOINT statementsand then rollback to previous state - variable will not be exist:

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---------122ROLLBACK TO sp1;SELECT pgv_get('pack','var_int',NULL::int);ERROR:  unrecognized variable"var_int"COMMIT;

You can undo removal of a transactional variable byROLLBACK, but if you removea whole package, all regular variables will be removed permanently:

SELECT pgv_set('pack','var_reg',123);SELECT pgv_set('pack','var_trans',456, true);BEGIN;SELECT pgv_free();SELECT*FROM pgv_list(); package | name | is_transactional---------+------+------------------(0 rows)-- Memory is allocated yetSELECT*FROM pgv_stats(); package | allocated_memory---------+------------------ pack    |24576ROLLBACK;SELECT*FROM pgv_list(); package |   name    | is_transactional---------+-----------+------------------ pack    | var_trans | t

If you created transactional variable once, you should use flagis_transactionalevery time when you want to change variable value by functionspgv_set(),pgv_insert() and deprecated setters (i.e.pgv_set_int()). If you try tochange this option, you'll get an error:

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 createdas TRANSACTIONAL