本文简单介绍了PostgreSQL的插件：pg_variables。该插件可用于定义变量，在创建类似Oracle的package等场景时用于声明变量。

**安装**
使用git下载源码，编译安装
```
[pg12@localhost contrib]$ git clone https://github.com/postgrespro/pg_variables.git
Cloning into 'pg_variables'...
remote: Enumerating objects: 585, done.
remote: Total 585 (delta 0), reused 0 (delta 0), pack-reused 585
Receiving objects: 100% (585/585), 328.79 KiB | 75.00 KiB/s, done.
Resolving deltas: 100% (404/404), done.
[pg12@localhost contrib]$ cd pg_variables/
[pg12@localhost pg_variables]$ make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/pg12/pg12.0/include/postgresql/server -I/appdb/pg12/pg12.0/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_variables.o pg_variables.c -MMD -MP -MF .deps/pg_variables.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/pg12/pg12.0/include/postgresql/server -I/appdb/pg12/pg12.0/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_variables_record.o pg_variables_record.c -MMD -MP -MF .deps/pg_variables_record.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o pg_variables.so pg_variables.o pg_variables_record.o  -L/appdb/pg12/pg12.0/lib    -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.0/lib',--enable-new-dtags  
cat pg_variables--1.0.sql pg_variables--1.0--1.1.sql pg_variables--1.1--1.2.sql > pg_variables--1.2.sql
[pg12@localhost pg_variables]$ make USE_PGXS=1 install
/bin/mkdir -p '/appdb/pg12/pg12.0/lib/postgresql'
/bin/mkdir -p '/appdb/pg12/pg12.0/share/postgresql/extension'
/bin/mkdir -p '/appdb/pg12/pg12.0/share/postgresql/extension'
/bin/install -c -m 755  pg_variables.so '/appdb/pg12/pg12.0/lib/postgresql/pg_variables.so'
/bin/install -c -m 644 .//pg_variables.control '/appdb/pg12/pg12.0/share/postgresql/extension/'
/bin/install -c -m 644 .//pg_variables--1.0.sql .//pg_variables--1.0--1.1.sql .//pg_variables--1.1--1.2.sql pg_variables--1.2.sql '/appdb/pg12/pg12.0/share/postgresql/extension/'
[pg12@localhost pg_variables]$ make USE_PGXS=1 installcheck
/appdb/pg12/pg12.0/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/appdb/pg12/pg12.0/bin'    --dbname=contrib_regression pg_variables pg_variables_any pg_variables_trans
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
psql: error: could not connect to server: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
command failed: "/appdb/pg12/pg12.0/bin/psql" -X -c "DROP DATABASE IF EXISTS \"contrib_regression\"" "postgres"
make: *** [installcheck] Error 2
[pg12@localhost pg_variables]$ pg_ctl start
waiting for server to start....2019-11-18 14:43:59.175 CST [2254] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2019-11-18 14:43:59.175 CST [2254] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-11-18 14:43:59.175 CST [2254] LOG:  listening on IPv6 address "::", port 5432
2019-11-18 14:43:59.176 CST [2254] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-11-18 14:43:59.769 CST [2254] LOG:  redirecting log output to logging collector process
2019-11-18 14:43:59.769 CST [2254] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg12@localhost pg_variables]$ make USE_PGXS=1 installcheck
/appdb/pg12/pg12.0/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/appdb/pg12/pg12.0/bin'    --dbname=contrib_regression pg_variables pg_variables_any pg_variables_trans
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
NOTICE:  database "contrib_regression" does not exist, skipping
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test pg_variables                 ... ok          161 ms
test pg_variables_any             ... ok           47 ms
test pg_variables_trans           ... ok          128 ms

=====================
 All 3 tests passed. 
=====================

[pg12@localhost pg_variables]$

```

**简单使用**
创建扩展
```
[local]:5432 pg12@testdb=# create extension pg_variables;
CREATE EXTENSION
[local]:5432 pg12@testdb=#

```
可以看到，该插件支持常规的数据类型，而对于复杂数据类型，则使用jsonb。
上述函数中，其中重要的函数主要有两个：pgv_set和pgv_get
```
[local]:5432 pg12@testdb=# \df pgv_get
                                                    List of functions
 Schema |  Name   | Result data type |                            Argument data types                             | Type 
--------+---------+------------------+----------------------------------------------------------------------------+------
 public | pgv_get | anyarray         | package text, name text, var_type anyarray, strict boolean DEFAULT true    | func
 public | pgv_get | anynonarray      | package text, name text, var_type anynonarray, strict boolean DEFAULT true | func
(2 rows)

[local]:5432 pg12@testdb=# select pgv_set('pk1','pk1_var1',1);
 pgv_set 
---------
 
(1 row)

[local]:5432 pg12@testdb=# select pgv_get('pk1','pk1_var1',null::int);
 pgv_get 
---------
       1
(1 row)

[local]:5432 pg12@testdb=# select pgv_set('pk1','pk1_var1',101);
 pgv_set 
---------
 
(1 row)

[local]:5432 pg12@testdb=# select pgv_get('pk1','pk1_var1',null::int);
 pgv_get 
---------
     101
(1 row)

[local]:5432 pg12@testdb=#

```
而且变量的作用域只在当前session中有效
```
[root@localhost ~]# su - pg12
Last login: Mon Nov 18 14:39:19 CST 2019 on pts/0
[pg12@localhost ~]$ psql -d testdb
Expanded display is used automatically.
psql (12.0)
Type "help" for help.

[local]:5432 pg12@testdb=# select pgv_get('pk1','pk1_var1',null::int);
ERROR:  unrecognized package "pk1"
[local]:5432 pg12@testdb=#

```

**参考资料**
[pg_variables(https://github.com/postgrespro/pg_variables)

ITPub博客

PostgreSQL DBA(129) - Extension(pg_variables).md

最新文章