Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Session wide variables for PostgreSQL

License

NotificationsYou must be signed in to change notification settings

postgrespro/pg_variables

Repository files navigation

Introduction

Thepg_variables module provides functions to work with variables of varioustypes. Created variables live only in the current user session.

Note that the module doesnot support transactions and savepoints. Forexample:

SELECT pgv_set('vars','int1',101);BEGIN;SELECT pgv_set('vars','int2',102);ROLLBACK;SELECT*FROM pgv_list()order by package, name; package | name---------+------ vars    | int1 vars    | int2(2 rows)

License

This module available under the same license asPostgreSQL.

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. The module supports the following scalar and record types.

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

FunctionReturns
pgv_set(package text, name text, value anynonarray)void
pgv_get(package text, name text, var_type anynonarray, strict bool default true)anynonarray

Deprecated scalar variables functions

Integer variables

FunctionReturns
pgv_set_int(package text, name text, value int)void
pgv_get_int(package text, name text, strict bool default true)int

Text variables

FunctionReturns
pgv_set_text(package text, name text, value text)void
pgv_get_text(package text, name text, strict bool default true)text

Numeric variables

FunctionReturns
pgv_set_numeric(package text, name text, value numeric)void
pgv_get_numeric(package text, name text, strict bool default true)numeric

Timestamp variables

FunctionReturns
pgv_set_timestamp(package text, name text, value timestamp)void
pgv_get_timestamp(package text, name text, strict bool default true)timestamp

Timestamp with timezone variables

FunctionReturns
pgv_set_timestamptz(package text, name text, value timestamptz)void
pgv_get_timestamptz(package text, name text, strict bool default true)timestamptz

Date variables

FunctionReturns
pgv_set_date(package text, name text, value date)void
pgv_get_date(package text, name text, strict bool default true)date

Jsonb variables

FunctionReturns
pgv_set_jsonb(package text, name text, value jsonb)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.

FunctionReturnsDescription
pgv_insert(package text, name text, r record)voidInserts 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)booleanUpdates 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)booleanDeletes 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 recordReturns the variable collection records.
pgv_select(package text, name text, value anynonarray)recordReturns 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 recordReturns the variable collection records with the corresponding primary keys (the first column ofr is a primary key).

Miscellaneous functions

FunctionReturnsDescription
pgv_exists(package text, name text)boolReturnstrue if package and variable exists.
pgv_exists(package text)boolReturnstrue if package exists.
pgv_remove(package text, name text)voidRemoves the variable with the corresponding name. Required package and variable must exists, otherwise the error will be raised.
pgv_remove(package text)voidRemoves the package and all package variables with the corresponding name. Required package must exists, otherwise the error will be raised.
pgv_free()voidRemoves 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 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 variables:

SELECT pgv_set('vars','int1',101);SELECT pgv_set('vars','int2',102);SELECT pgv_get('vars','int1',NULL::int); pgv_get_int-------------101(1 row)SELECT pgv_get('vars','int2',NULL::int); pgv_get_int-------------102(1 row)

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)(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---------+------ vars    | int1 vars    | int2 vars    | r1(3 rows)

And get used memory in bytes:

SELECT*FROM pgv_stats()order by package; package | used_memory---------+------------- vars    |16736(1 row)

You can delete variables or hole packages:

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

You can delete all packages and variables:

SELECT pgv_free();
[8]ページ先頭
©2009-2025 Movatter.jp