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

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

[pg12@localhostcontrib]$gitclonehttps://github.com/postgrespro/pg_variables.gitCloninginto'pg_variables'...remote:Enumeratingobjects:585,done.remote:Total585(delta0),reused0(delta0),pack-reused585Receivingobjects:100%(585/585),328.79KiB|75.00KiB/s,done.Resolvingdeltas:100%(404/404),done.[pg12@localhostcontrib]$cdpg_variables/[pg12@localhostpg_variables]$makeUSE_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-opg_variables.opg_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-opg_variables_record.opg_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-opg_variables.sopg_variables.opg_variables_record.o-L/appdb/pg12/pg12.0/lib-Wl,--as-needed-Wl,-rpath,'/appdb/pg12/pg12.0/lib',--enable-new-dtagscatpg_variables--1.0.sqlpg_variables--1.0--1.1.sqlpg_variables--1.1--1.2.sql>pg_variables--1.2.sql[pg12@localhostpg_variables]$makeUSE_PGXS=1install/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-m755pg_variables.so'/appdb/pg12/pg12.0/lib/postgresql/pg_variables.so'/bin/install-c-m644.//pg_variables.control'/appdb/pg12/pg12.0/share/postgresql/extension/'/bin/install-c-m644.//pg_variables--1.0.sql.//pg_variables--1.0--1.1.sql.//pg_variables--1.1--1.2.sqlpg_variables--1.2.sql'/appdb/pg12/pg12.0/share/postgresql/extension/'[pg12@localhostpg_variables]$makeUSE_PGXS=1installcheck/appdb/pg12/pg12.0/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress--inputdir=./--bindir='/appdb/pg12/pg12.0/bin'--dbname=contrib_regressionpg_variablespg_variables_anypg_variables_trans(usingpostmasteronUnixsocket,defaultport)==============droppingdatabase"contrib_regression"==============psql:error:couldnotconnecttoserver:couldnotconnecttoserver:NosuchfileordirectoryIstheserverrunninglocallyandacceptingconnectionsonUnixdomainsocket"/tmp/.s.PGSQL.5432"?commandfailed:"/appdb/pg12/pg12.0/bin/psql"-X-c"DROPDATABASEIFEXISTS\"contrib_regression\"""postgres"make:***[installcheck]Error2[pg12@localhostpg_variables]$pg_ctlstartwaitingforservertostart....2019-11-1814:43:59.175CST[2254]LOG:startingPostgreSQL12.0onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-11-1814:43:59.175CST[2254]LOG:listeningonIPv4address"0.0.0.0",port54322019-11-1814:43:59.175CST[2254]LOG:listeningonIPv6address"::",port54322019-11-1814:43:59.176CST[2254]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-11-1814:43:59.769CST[2254]LOG:redirectinglogoutputtologgingcollectorprocess2019-11-1814:43:59.769CST[2254]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhostpg_variables]$makeUSE_PGXS=1installcheck/appdb/pg12/pg12.0/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress--inputdir=./--bindir='/appdb/pg12/pg12.0/bin'--dbname=contrib_regressionpg_variablespg_variables_anypg_variables_trans(usingpostmasteronUnixsocket,defaultport)==============droppingdatabase"contrib_regression"==============NOTICE:database"contrib_regression"doesnotexist,skippingDROPDATABASE==============creatingdatabase"contrib_regression"==============CREATEDATABASEALTERDATABASE==============runningregressiontestqueries==============testpg_variables...ok161mstestpg_variables_any...ok47mstestpg_variables_trans...ok128ms=====================All3testspassed.=====================[pg12@localhostpg_variables]$

简单使用
创建扩展

[local]:5432pg12@testdb=#createextensionpg_variables;CREATEEXTENSION[local]:5432pg12@testdb=#

pg_variables中包含了多个函数

[local]:5432pg12@testdb=#\dfpgv*ListoffunctionsSchema|Name|Resultdatatype|Argumentdatatypes|Type--------+---------------------+----------------------------------------------------------+----------------------------------------------------------------------------------------------------+------public|pgv_delete|boolean|packagetext,nametext,valueanynonarray|funcpublic|pgv_exists|boolean|packagetext|funcpublic|pgv_exists|boolean|packagetext,nametext|funcpublic|pgv_free|void||funcpublic|pgv_get|anyarray|packagetext,nametext,var_typeanyarray,strictbooleanDEFAULTtrue|funcpublic|pgv_get|anynonarray|packagetext,nametext,var_typeanynonarray,strictbooleanDEFAULTtrue|funcpublic|pgv_get_date|date|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_int|integer|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_jsonb|jsonb|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_numeric|numeric|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_text|text|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_timestamp|timestampwithouttimezone|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_get_timestamptz|timestampwithtimezone|packagetext,nametext,strictbooleanDEFAULTtrue|funcpublic|pgv_insert|void|packagetext,nametext,rrecord,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_list|TABLE(packagetext,nametext,is_transactionalboolean)||funcpublic|pgv_remove|void|packagetext|funcpublic|pgv_remove|void|packagetext,nametext|funcpublic|pgv_select|SETOFrecord|packagetext,nametext|funcpublic|pgv_select|SETOFrecord|packagetext,nametext,valueanyarray|funcpublic|pgv_select|record|packagetext,nametext,valueanynonarray|funcpublic|pgv_set|void|packagetext,nametext,valueanyarray,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set|void|packagetext,nametext,valueanynonarray,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_date|void|packagetext,nametext,valuedate,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_int|void|packagetext,nametext,valueinteger,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_jsonb|void|packagetext,nametext,valuejsonb,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_numeric|void|packagetext,nametext,valuenumeric,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_text|void|packagetext,nametext,valuetext,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_timestamp|void|packagetext,nametext,valuetimestampwithouttimezone,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set_timestamptz|void|packagetext,nametext,valuetimestampwithtimezone,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_stats|TABLE(packagetext,allocated_memorybigint)||funcpublic|pgv_update|boolean|packagetext,nametext,rrecord|func(31rows)

可以看到，该插件支持常规的数据类型，而对于复杂数据类型，则使用jsonb。
上述函数中，其中重要的函数主要有两个：pgv_set和pgv_get

[local]:5432pg12@testdb=#\dfpgv_getListoffunctionsSchema|Name|Resultdatatype|Argumentdatatypes|Type--------+---------+------------------+----------------------------------------------------------------------------+------public|pgv_get|anyarray|packagetext,nametext,var_typeanyarray,strictbooleanDEFAULTtrue|funcpublic|pgv_get|anynonarray|packagetext,nametext,var_typeanynonarray,strictbooleanDEFAULTtrue|func(2rows)[local]:5432pg12@testdb=#\dfpgv_setListoffunctionsSchema|Name|Resultdatatype|Argumentdatatypes|Type--------+---------+------------------+------------------------------------------------------------------------------------+------public|pgv_set|void|packagetext,nametext,valueanyarray,is_transactionalbooleanDEFAULTfalse|funcpublic|pgv_set|void|packagetext,nametext,valueanynonarray,is_transactionalbooleanDEFAULTfalse|func(2rows)[local]:5432pg12@testdb=#selectpgv_set('pk1','pk1_var1',1);pgv_set---------(1row)[local]:5432pg12@testdb=#selectpgv_get('pk1','pk1_var1',null::int);pgv_get---------1(1row)[local]:5432pg12@testdb=#selectpgv_set('pk1','pk1_var1',101);pgv_set---------(1row)[local]:5432pg12@testdb=#selectpgv_get('pk1','pk1_var1',null::int);pgv_get---------101(1row)[local]:5432pg12@testdb=#

而且变量的作用域只在当前session中有效

[root@localhost~]#su-pg12Lastlogin:MonNov1814:39:19CST2019onpts/0[pg12@localhost~]$psql-dtestdbExpandeddisplayisusedautomatically.psql(12.0)Type"help"forhelp.[local]:5432pg12@testdb=#selectpgv_get('pk1','pk1_var1',null::int);ERROR:unrecognizedpackage"pk1"[local]:5432pg12@testdb=#

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