Session Level Variables qhb-variables

WARNING!
In QHB 1.5.3 release, this feature is experimental and is not recommended for use in a production installation.

Description

qhb-variables allows you to create variables that will be deleted when the session ends. This mechanism is an alternative to temporary tables in places where their performance is insufficient. Variables are stored in packages. There are two types of variables: scalar and table. Scalar variables can store only one value of any type at a time, while table variables can store multiple rows. Variables are also divided into transactional and non-transactional. The state of the former does not depend on the result of the transaction, while the latter change their value depending on COMMIT and ROLLBACK. The type of a variable cannot be changed after creation until it is completely deleted (in the case of a transactional variable, it means before executing COMMIT).

---

---

qhb-variables Functions

Functions for Scalar Variables

var_set ( package text, variable text, value (non)anynonarray, is_transactional bool default false ) → void

Sets the variable value, including updating.

var_get ( package text, variable text, var_type (non)anynonarray, strict bool default true ) → anynonarray

Gets the variable value.

Usage Examples

SELECT var_set('pack', 'var', 0);
 var_set
---------

(1 row)

SELECT var_get('pack', 'var', NULL::int);
 var_get
---------
       0
(1 row)

BEGIN;
SELECT var_set('pack', 'var', 'before savepoint'::text, true);
 var_set
---------

(1 row)

SAVEPOINT zero;
SELECT var_set('pack', 'var', 'after savepoint'::text, true);
 var_set
---------

(1 row)

SELECT var_get('pack', 'var', NULL::text);
     var_get     
-----------------
 after savepoint
(1 row)

ROLLBACK TO zero;
SELECT var_get('pack', 'var', NULL::text);
     var_get      
------------------
 before savepoint
(1 row)

---

Functions for Table Variables

var_insert ( package text, variable text, record record, is_transactional bool default false ) → void

Inserts a new record into a table variable. The first record column is the primary key. Returns an error if a record with that key already exists or if the record has a different signature.

var_update ( package text, variable text, record record ) → boolean

Updates a record in a table variable based on a key field. Returns true if the record was updated. If the variable signature differs from the provided string, an error occurs.

var_delete ( package text, variable text, value anynonarray ) → boolean

Deletes a record based on the key. Returns true if the record was deleted.

var_select ( package text, variable text ) → set of record

Returns the current variable value.

var_select ( package text, variable text, value anynonarray ) → record

Returns the current variable value based on the key.

var_select ( package text, variable text, value anyarray ) → set of record

Returns the current variable value based on the keys.

Usage Examples

SELECT var_insert('pac', 'var', ROW(NULL::int, 0::int));
 var_insert
------------

(1 row)

SELECT var_insert('pac', 'var', ROW(0::int, 1::int));
 var_insert
------------

(1 row)

SELECT var_insert('pac', 'var1', ROW(0::int, 2::int));
 var_insert
------------

(1 row)

SELECT var_insert('pac', 'var1', ROW(2::int, 2::int));
 var_insert
------------

(1 row)

SELECT var_select('pac', 'var');
 var_select
------------
 (,0)
 (0,1)
(2 rows)

SELECT var_select('pac', 'var1', 0);
 var_select
------------
 (0,2)
(1 row)

SELECT var_select('pac', 'var1', ARRAY[0, 1]);
 var_select
------------
 (0,2)
(1 row)

---

Common Variable Functions

var_exists ( package text, variable text, is_record bool ) → bool

Returns true if the variable exists in the given package.

var_exists ( package text ) → bool

Returns true if the package exists.

var_remove ( package text, variable text, is_record bool) → void

Removes a variable with that name. If the variable or package does not exist, an error will be returned.

var_remove ( package text ) → void

Removes the package with all variables that are contained in it. If the package does not exist, an error will be returned.

var_free () → void

Removes all packages with all their variables.

var_list () → table ( package text, variable text, var_type text, is_transactional bool )

Returns a list of all packages and variables, indicating their transactionality and type.

var_stats () → table ( alloc_mem bigint )

Returns the amount of memory occupied by variables. Note that this function returns the allocated space, which may not be fully used. For more information, use pg_backend_memory_contexts() function.

There can be two variables with the same name in a package, but only if one of them is a table variable and the other is a scalar variable. By default, the var_exists and var_remove functions remove both variables, so if you need to select only a table variable, pass true as the last parameter, and to remove only a scalar variable, pass false. This parameter is optional.

Example:

BEGIN;
SELECT var_set('pack', 'var', 0, true);
 var_set
---------

(1 row)

SELECT var_insert('pack', 'var', ROW(0::int, 1::int), true);
 var_insert
------------

(1 row)

SELECT * FROM var_list();
 package | variable |         variable_type         | is_transactional
---------+----------+-------------------------------+------------------
 pack    | var      | Scalar(integer)               | t
 pack    | var      | Record(row(integer, integer)) | t
(2 rows)

SAVEPOINT first;
SELECT var_remove('pack', 'var', true);
 var_remove
------------

(1 row)

SELECT var_exists('pack', 'var', true);
 var_exists
------------
 f
(1 row)

SELECT var_exists('pack', 'var', false);
 var_exists
------------
 t
(1 row)

SELECT var_exists('pack', 'var');
 var_exists
------------
 t
(1 row)

SELECT var_remove('pack', 'var');
 var_remove
------------

(1 row)

SELECT var_exists('pack', 'var', false);
 var_exists
------------
 f
(1 row)

ROLLBACK TO first;
SELECT var_exists('pack', 'var');
 var_exists
------------
 t
(1 row)

Usage Examples

BEGIN;
SELECT var_set('pack', 'var', 'non-transactional'::text);
 var_set
---------

(1 row)

SELECT var_set('pack', 'var1', 'transactional'::text, true);
 var_set
---------

(1 row)

SELECT * FROM var_list();
 package | variable | variable_type | is_transactional
---------+----------+---------------+------------------
 pack    | var      | Scalar(text)  | f
 pack    | var1     | Scalar(text)  | t
(2 rows)

SAVEPOINT first;
SELECT var_free();
 var_free
----------

(1 row)

SELECT var_stats();
 var_stats
-----------
      8192
(1 row)

SELECT * FROM var_list();
 package | variable | variable_type | is_transactional
---------+----------+---------------+------------------
(0 rows)

ROLLBACK TO first;
SELECT * FROM var_list();
 package | variable | variable_type | is_transactional
---------+----------+---------------+------------------
 pack    | var1     | Scalar(text)  | t
(1 row)

---

---

Other Usage Examples

To insert from table and destruct SELECT:

CREATE TABLE test_table (id int, t text);
INSERT INTO test_table VALUES (0, 'first'), (1, 'second');
SELECT var_insert('pack', 'var', test_table) FROM test_table;
 var_insert
------------


(2 rows)

SELECT var_select('pack', 'var');
 var_select
------------
 (1,second)
 (0,first)
(2 rows)

SELECT * FROM var_select('pack', 'var') AS (id int, t text);
 id |   t    
----+--------
  1 | second
  0 | first
(2 rows)

To change the type while completely deleting a variable:

SELECT var_set('pack', 'var', 1::int);
 var_set
---------

(1 row)

SELECT var_set('pack', 'var_transactional', 1::int, true);
 var_set
---------

(1 row)

SELECT * FROM var_list();
 package |     variable      |  variable_type  | is_transactional
---------+-------------------+-----------------+------------------
 pack    | var_transactional | Scalar(integer) | t
 pack    | var               | Scalar(integer) | f
(2 rows)

BEGIN;
SELECT var_free();
 var_free
----------

(1 row)

SELECT var_set('pack', 'var', 1::text); -- successful
 var_set
---------

(1 row)

-- SELECT var_set('pack', 'var_transactional', 1::text, true); -- error
COMMIT;
SELECT var_set('pack', 'var_transactional', 1::text, true); -- successful
 var_set
---------

(1 row)

SELECT * FROM var_list();
 package |     variable      | variable_type | is_transactional
---------+-------------------+---------------+------------------
 pack    | var_transactional | Scalar(text)  | t
 pack    | var               | Scalar(text)  | f
(2 rows)

This example works because the variables were permanently deleted when calling var_free.

To save table elements. Inserting into a variable creates an independent copy of the data.

CREATE TABLE test_table (id int, t text);
INSERT INTO test_table VALUES (0, 'first'), (1, 'second'), (2, 'third');
SELECT var_insert('pack', 'var', test_table) FROM test_table WHERE id < 2;
 var_insert
------------


(2 rows)

DROP TABLE test_table;
CREATE TABLE test_table AS SELECT * FROM var_select('pack', 'var') AS (id int, t text);
SELECT * FROM test_table;
 id |   t    
----+--------
  1 | second
  0 | first
(2 rows)

SELECT + CURSOR

When calling var_select, an instant copy of the data is created, so var_update will not affect to the result of SELECT. However, if the variable has been deleted, then it will be impossible to retrieve the remaining data.

CREATE TABLE test_table (id int, t text);
INSERT INTO test_table VALUES (0, 'first'), (1, 'second'), (2, 'third');
SELECT var_insert('pack', 'var', test_table, true) FROM test_table;
 var_insert
------------



(3 rows)

BEGIN;
DECLARE r1_cur CURSOR FOR SELECT var_select('pack', 'var');
FETCH 1 in r1_cur;
 var_select
------------
 (2,third)
(1 row)

SELECT var_remove('pack');
 var_remove
------------

(1 row)

FETCH 1 in r1_cur;
ERROR:  var_select: Existence: Variable with name `var` does not exist in package `pack`
ROLLBACK;
SELECT var_select('pack', 'var');
 var_select
------------
 (2,third)
 (1,second)
 (0,first)
(3 rows)