本文简单介绍了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=# ``` pg_variables中包含了多个函数 ``` [local]:5432 pg12@testdb=# \df pgv* List of functions Schema | Name | Result data type | Argument data types | Type --------+---------------------+----------------------------------------------------------+----------------------------------------------------------------------------------------------------+------ public | pgv_delete | boolean | package text, name text, value anynonarray | func public | pgv_exists | boolean | package text | func public | pgv_exists | boolean | package text, name text | func public | pgv_free | void | | func 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 public | pgv_get_date | date | package text, name text, strict boolean DEFAULT true | func public | pgv_get_int | integer | package text, name text, strict boolean DEFAULT true | func public | pgv_get_jsonb | jsonb | package text, name text, strict boolean DEFAULT true | func public | pgv_get_numeric | numeric | package text, name text, strict boolean DEFAULT true | func public | pgv_get_text | text | package text, name text, strict boolean DEFAULT true | func public | pgv_get_timestamp | timestamp without time zone | package text, name text, strict boolean DEFAULT true | func public | pgv_get_timestamptz | timestamp with time zone | package text, name text, strict boolean DEFAULT true | func public | pgv_insert | void | package text, name text, r record, is_transactional boolean DEFAULT false | func public | pgv_list | TABLE(package text, name text, is_transactional boolean) | | func public | pgv_remove | void | package text | func public | pgv_remove | void | package text, name text | func public | pgv_select | SETOF record | package text, name text | func public | pgv_select | SETOF record | package text, name text, value anyarray | func public | pgv_select | record | package text, name text, value anynonarray | func public | pgv_set | void | package text, name text, value anyarray, is_transactional boolean DEFAULT false | func public | pgv_set | void | package text, name text, value anynonarray, is_transactional boolean DEFAULT false | func public | pgv_set_date | void | package text, name text, value date, is_transactional boolean DEFAULT false | func public | pgv_set_int | void | package text, name text, value integer, is_transactional boolean DEFAULT false | func public | pgv_set_jsonb | void | package text, name text, value jsonb, is_transactional boolean DEFAULT false | func public | pgv_set_numeric | void | package text, name text, value numeric, is_transactional boolean DEFAULT false | func public | pgv_set_text | void | package text, name text, value text, is_transactional boolean DEFAULT false | func public | pgv_set_timestamp | void | package text, name text, value timestamp without time zone, is_transactional boolean DEFAULT false | func public | pgv_set_timestamptz | void | package text, name text, value timestamp with time zone, is_transactional boolean DEFAULT false | func public | pgv_stats | TABLE(package text, allocated_memory bigint) | | func public | pgv_update | boolean | package text, name text, r record | func (31 rows) ``` 可以看到，该插件支持常规的数据类型，而对于复杂数据类型，则使用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=# \df pgv_set List of functions Schema | Name | Result data type | Argument data types | Type --------+---------+------------------+------------------------------------------------------------------------------------+------ public | pgv_set | void | package text, name text, value anyarray, is_transactional boolean DEFAULT false | func public | pgv_set | void | package text, name text, value anynonarray, is_transactional boolean DEFAULT false | 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)