F.43. pg_variables

F.43. pg_variables
Prev	Up	Appendix F. Additional Supplied Modules	Home	Next

F.43.1. Introduction
F.43.2. Installation
F.43.3. Functions
F.43.4. Examples
F.43.5. Authors

F.43.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.43.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.43.3. 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.43.3.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.43.3.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.