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

Commit3b1f493

Browse files
author
Artur Zakirov
committed
pg_variables commited
0 parents  commit3b1f493

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+
ifdefUSE_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+
CREATETABLEtab (idint, tvarchar);
139+
INSERT INTO tabVALUES (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)
[8]ページ先頭
©2009-2025 Movatter.jp