Skip to content

Commit 3b1f493

Browse files
author
Artur Zakirov
committed
pg_variables commited
0 parents  commit 3b1f493

12 files changed

+3134
-0
lines changed

‎Makefile

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
# contrib/pg_variables/Makefile
2+
3+
MODULE_big = pg_variables
4+
OBJS = pg_variables.o pg_variables_record.o $(WIN32RES)
5+
6+
EXTENSION = pg_variables
7+
DATA = pg_variables--1.0.sql
8+
PGFILEDESC = "pg_variables - sessional variables"
9+
10+
REGRESS = pg_variables
11+
12+
ifdef USE_PGXS
13+
PG_CONFIG = pg_config
14+
PGXS := $(shell $(PG_CONFIG) --pgxs)
15+
include $(PGXS)
16+
else
17+
subdir = contrib/pg_variables
18+
top_builddir = ../..
19+
include $(top_builddir)/src/Makefile.global
20+
include $(top_srcdir)/contrib/contrib-global.mk
21+
endif

‎README.md

Lines changed: 214 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,214 @@
1+
# pg_variables - session variables with various types
2+
3+
## Introduction
4+
5+
The **pg_variables** module provides functions to work with variables of various
6+
types. Created variables live only in the current user session.
7+
8+
## License
9+
10+
This module available under the same license as
11+
[PostgreSQL](http://www.postgresql.org/about/licence/).
12+
13+
## Installation
14+
15+
Typical installation procedure may look like this:
16+
17+
$ cd pg_variables
18+
$ make USE_PGXS=1
19+
$ sudo make USE_PGXS=1 install
20+
$ make USE_PGXS=1 installcheck
21+
$ psql DB -c "CREATE EXTENSION pg_variables;"
22+
23+
## Module functions
24+
25+
### Integer variables
26+
27+
Function | Returns
28+
-------- | -------
29+
`pgv_set_int(package text, name text, value int)` | `void`
30+
`pgv_get_int(package text, name text, strict bool default true)` | `int`
31+
32+
### Text variables
33+
34+
Function | Returns
35+
-------- | -------
36+
`pgv_set_text(package text, name text, value text)` | `void`
37+
`pgv_get_text(package text, name text, strict bool default true)` | `text`
38+
39+
### Numeric variables
40+
41+
Function | Returns
42+
-------- | -------
43+
`pgv_set_numeric(package text, name text, value numeric)` | `void`
44+
`pgv_get_numeric(package text, name text, strict bool default true)` | `numeric`
45+
46+
### Timestamp variables
47+
48+
Function | Returns
49+
-------- | -------
50+
`pgv_set_timestamp(package text, name text, value timestamp)` | `void`
51+
`pgv_get_timestamp(package text, name text, strict bool default true)` | `timestamp`
52+
53+
### Timestamp with timezone variables
54+
55+
Function | Returns
56+
-------- | -------
57+
`pgv_set_timestamptz(package text, name text, value timestamptz)` | `void`
58+
`pgv_get_timestamptz(package text, name text, strict bool default true)` | `timestamptz`
59+
60+
### Date variables
61+
62+
Function | Returns
63+
-------- | -------
64+
`pgv_set_date(package text, name text, value date)` | `void`
65+
`pgv_get_date(package text, name text, strict bool default true)` | `date`
66+
67+
### Jsonb variables
68+
69+
Function | Returns
70+
-------- | -------
71+
`pgv_set_jsonb(package text, name text, value jsonb)` | `void`
72+
`pgv_get_jsonb(package text, name text, strict bool default true)` | `jsonb`
73+
74+
### Records
75+
76+
Function | Returns
77+
-------- | -------
78+
`pgv_insert(package text, name text, r record)` | `void`
79+
`pgv_update(package text, name text, r record)` | `boolean`
80+
`pgv_delete(package text, name text, value anynonarray)` | `boolean`
81+
`pgv_select(package text, name text)` | `set of record`
82+
`pgv_select(package text, name text, value anynonarray)` | `record`
83+
`pgv_select(package text, name text, value anyarray)` | `set of record`
84+
85+
### Miscellaneous functions
86+
87+
Function | Returns
88+
-------- | -------
89+
`pgv_exists(package text, name text)` | `bool`
90+
`pgv_remove(package text, name text)` | `void`
91+
`pgv_remove(package text)` | `void`
92+
`pgv_free()` | `void`
93+
`pgv_list()` | `table(package text, name text)`
94+
`pgv_stats()` | `table(package text, used_memory bigint)`
95+
96+
## Error messages
97+
98+
* "array argument can not be NULL"
99+
* "could not identify a hash function for type %s"
100+
* "could not identify a matching function for type %s"
101+
* "function returning record called in context that cannot accept type record"
102+
* "name "%s" is too long"
103+
* "new record structure differs from variable "%s" structure"
104+
* "package name can not be NULL"
105+
* "record argument can not be NULL"
106+
* "requested value type differs from variable "%s" key type"
107+
* "searching for elements in multidimensional arrays is not supported"
108+
* "there is a record in the variable "%s" with same key"
109+
* "unrecognized package "%s""
110+
* "unrecognized variable "%s""
111+
* "variable "%s" requires %s value"
112+
* "variable name can not be NULL"
113+
114+
## Examples
115+
116+
It is easy to use functions to work with scalar variables:
117+
118+
```sql
119+
SELECT pgv_set_int('vars', 'int1', 101);
120+
SELECT pgv_set_int('vars', 'int2', 102);
121+
122+
SELECT pgv_get_int('vars', 'int1');
123+
pgv_get_int
124+
-------------
125+
101
126+
(1 row)
127+
128+
SELECT pgv_get_int('vars', 'int2');
129+
pgv_get_int
130+
-------------
131+
102
132+
(1 row)
133+
```
134+
135+
Let's assume we have a **tab** table:
136+
137+
```sql
138+
CREATE TABLE tab (id int, t varchar);
139+
INSERT INTO tab VALUES (0, 'str00'), (1, 'str11');
140+
```
141+
142+
Then you can use functions to work with record variables:
143+
144+
```sql
145+
SELECT pgv_insert('vars', 'r1', tab) FROM tab;
146+
147+
SELECT pgv_select('vars', 'r1');
148+
pgv_select
149+
------------
150+
(1,str11)
151+
(0,str00)
152+
(2 rows)
153+
154+
SELECT pgv_select('vars', 'r1', 1);
155+
pgv_select
156+
------------
157+
(1,str11)
158+
(1 row)
159+
160+
SELECT pgv_select('vars', 'r1', 0);
161+
pgv_select
162+
------------
163+
(0,str00)
164+
(1 row)
165+
166+
SELECT pgv_select('vars', 'r1', ARRAY[1, 0]);
167+
pgv_select
168+
------------
169+
(1,str11)
170+
(0,str00)
171+
(2 rows)
172+
173+
SELECT pgv_delete('vars', 'r1', 1);
174+
175+
SELECT pgv_select('vars', 'r1');
176+
pgv_select
177+
------------
178+
(0,str00)
179+
(1 row)
180+
```
181+
182+
You can list packages and variables:
183+
184+
```sql
185+
SELECT * FROM pgv_list() order by package, name;
186+
package | name
187+
---------+------
188+
vars | int1
189+
vars | int2
190+
vars | r1
191+
(3 rows)
192+
```
193+
194+
And get used memory in bytes:
195+
196+
```sql
197+
SELECT * FROM pgv_stats() order by package;
198+
package | used_memory
199+
---------+-------------
200+
vars | 16736
201+
(1 row)
202+
```
203+
204+
You can delete variables or hole packages:
205+
206+
```sql
207+
SELECT pgv_remove('vars', 'int1');
208+
SELECT pgv_remove('vars');
209+
```
210+
211+
You can delete all packages and variables:
212+
```sql
213+
SELECT pgv_free();
214+
```

0 commit comments

Comments
 (0)