PostgreSQL的插件pg_variables有什么作用

本篇内容主要讲解"PostgreSQL的插件pg_variables有什么作用"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"PostgreSQL的插件pg_variables有什么作用"吧!

安装
使用git下载源码，编译安装

[pg12@localhost contrib]$ git clone https://github.com/postgrespro/pg_variables.gitCloning into 'pg_variables'...remote: Enumerating objects: 585, done.remote: Total 585 (delta 0), reused 0 (delta 0), pack-reused 585Receiving 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=1gcc -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.Pogcc -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.Pogcc -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 startwaiting 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-bit2019-11-18 14:43:59.175 CST [2254] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-11-18 14:43:59.175 CST [2254] LOG:  listening on IPv6 address "::", port 54322019-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 process2019-11-18 14:43:59.769 CST [2254] HINT:  Future log output will appear in directory "pg_log". doneserver 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, skippingDROP DATABASE============== creating database "contrib_regression" ==============CREATE DATABASEALTER DATABASE============== running regression test queries        ==============test pg_variables                 ... ok          161 mstest pg_variables_any             ... ok           47 mstest 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 - pg12Last login: Mon Nov 18 14:39:19 CST 2019 on pts/0[pg12@localhost ~]$ psql -d testdbExpanded 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=#

到此，相信大家对"PostgreSQL的插件pg_variables有什么作用"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！