这篇文章主要讲解了“怎么使用PostgreSQL扩展函数”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么使用PostgreSQL扩展函数”吧!

例子

../env11.shgitclonehttps://github.com/MasaoFujii/pg_cheat_funcscdpg_cheat_funcs/USE_PGXS=1makeUSE_PGXS=1makeinstall

postgres=#createextensionpg_cheat_funcs;CREATEEXTENSION

1、打印内存上下文

postgres=#select*frompg_stat_get_memory_context();name|parent|level|total_bytes|total_nblocks|free_bytes|free_chunks|used_bytes--------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------TopMemoryContext||0|312552|11|40520|21|272032dynahash|TopMemoryContext|1|8192|1|1456|0|6736TopTransactionContext|TopMemoryContext|1|8192|1|7744|1|448PL/pgSQLfunction|TopMemoryContext|1|16384|2|7176|1|9208dynahash|TopMemoryContext|1|8192|1|560|0|7632dynahash|TopMemoryContext|1|8192|1|560|0|7632dynahash|TopMemoryContext|1|24528|2|2624|0|21904dynahash|TopMemoryContext|1|8192|1|2096|0|6096dynahash|TopMemoryContext|1|8192|1|1584|0|6608dynahash|TopMemoryContext|1|24576|2|10760|3|13816RowDescriptionContext|TopMemoryContext|1|8192|1|6896|0|1296MessageContext|TopMemoryContext|1|32768|3|10904|1|21864dynahash|TopMemoryContext|1|8192|1|560|0|7632dynahash|TopMemoryContext|1|32768|3|16832|8|15936TransactionAbortContext|TopMemoryContext|1|32768|1|32512|0|256dynahash|TopMemoryContext|1|8192|1|560|0|7632TopPortalContext|TopMemoryContext|1|8192|1|7664|0|528PortalContext|TopPortalContext|2|1024|1|592|0|432ExecutorState|PortalContext|3|49208|4|15576|3|33632printtup|ExecutorState|4|8192|1|7936|0|256Tablefunctionarguments|ExecutorState|4|8192|1|7936|0|256ExprContext|ExecutorState|4|8192|1|4536|0|3656dynahash|TopMemoryContext|1|16384|2|3512|2|12872CacheMemoryContext|TopMemoryContext|1|524288|7|20960|26|503328indexinfo|CacheMemoryContext|2|2048|2|624|1|1424indexinfo|CacheMemoryContext|2|2048|2|624|1|1424indexinfo|CacheMemoryContext|2|2048|2|624|1|1424indexinfo|CacheMemoryContext|2|2048|2|624|1|1424indexinfo|CacheMemoryContext|2|2048|2|680|1|1368EventTriggerCache|CacheMemoryContext|2|8192|1|7936|2|256dynahash|EventTriggerCache|3|8192|1|2624|0|5568indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096relationrules|CacheMemoryContext|2|16384|5|7352|0|9032indexinfo|CacheMemoryContext|2|2048|2|648|2|1400indexinfo|CacheMemoryContext|2|2048|2|704|3|1344indexinfo|CacheMemoryContext|2|1024|1|16|0|1008indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|592|3|1456indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|3072|2|1136|2|1936indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|704|3|1344indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|1024|1|16|0|1008indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|3072|2|1136|2|1936indexinfo|CacheMemoryContext|2|3072|2|1136|2|1936indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|704|3|1344indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|728|1|1320indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|624|2|1424indexinfo|CacheMemoryContext|2|2048|2|672|3|1376indexinfo|CacheMemoryContext|2|2048|2|704|3|1344indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|624|2|1424indexinfo|CacheMemoryContext|2|1024|1|16|0|1008indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|3072|2|1136|2|1936indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|728|1|1320indexinfo|CacheMemoryContext|2|2048|2|728|1|1320indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|680|2|1368indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|728|1|1320indexinfo|CacheMemoryContext|2|1024|1|48|0|976indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|704|3|1344indexinfo|CacheMemoryContext|2|1024|1|16|0|1008indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|760|2|1288indexinfo|CacheMemoryContext|2|1024|1|16|0|1008indexinfo|CacheMemoryContext|2|2048|2|728|1|1320indexinfo|CacheMemoryContext|2|2048|2|952|1|1096indexinfo|CacheMemoryContext|2|2048|2|952|1|1096WALrecordconstruction|TopMemoryContext|1|49768|2|6368|0|43400dynahash|TopMemoryContext|1|8192|1|2624|0|5568MdSmgr|TopMemoryContext|1|8192|1|7432|0|760dynahash|TopMemoryContext|1|8192|1|560|0|7632dynahash|TopMemoryContext|1|104120|2|2624|0|101496ErrorContext|TopMemoryContext|1|8192|1|7936|0|256(121rows)

2、文本压缩

postgres=#selectpglz_compress(repeat(md5(random()::text),1024));pglz_compress-------------------------------------------------------------------------------------------------------------------------------------\x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7(1row)

3、解压缩

pglz_decompress(bytea)postgres=#selectpglz_compress(repeat(md5(random()::text),8));pglz_compress--------------------------------------------------------------------------------------------\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce(1row)postgres=#selectpglz_decompress('\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce');pglz_decompress------------------------------------------------------------------------------26fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a897(1row)

4、查看绑定变量QUERY的PLAN COSE。

postgres=#preparea(name)asselect*frompg_classwhererelname=$1;PREPAREpostgres=#select*frompg_cached_plan_source('a');generic_cost|total_custom_cost|num_custom_plans|force_generic|force_custom--------------+-------------------+------------------+---------------+---------------1|0|0|f|f(1row)postgres=#executea('abc');relname|relnamespace|reltype|reloftype|relowner|relam|relfilenode|reltablespace|relpages|reltuples|relallvisible|reltoastrelid|relhasindex|relisshared|relpersistence|relkind|relnatts|relchecks|relhasoids|relhasrules|relhastriggers|relhassubclass|relrowsecurity|relforcerowsecurity|relispopulated|relreplident|relispartition|relrewrite|relfrozenxid|relminmxid|relacl|reloptions|relpartbound---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------(0rows)postgres=#select*frompg_cached_plan_source('a');generic_cost|total_custom_cost|num_custom_plans|force_generic|force_custom--------------+-------------------+------------------+---------------+---------------1|4.61208554676785|1|f|f(1row)postgres=#executea('abc');relname|relnamespace|reltype|reloftype|relowner|relam|relfilenode|reltablespace|relpages|reltuples|relallvisible|reltoastrelid|relhasindex|relisshared|relpersistence|relkind|relnatts|relchecks|relhasoids|relhasrules|relhastriggers|relhassubclass|relrowsecurity|relforcerowsecurity|relispopulated|relreplident|relispartition|relrewrite|relfrozenxid|relminmxid|relacl|reloptions|relpartbound---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------(0rows)postgres=#select*frompg_cached_plan_source('a');generic_cost|total_custom_cost|num_custom_plans|force_generic|force_custom--------------+-------------------+------------------+---------------+---------------1|9.22417109353571|2|f|f(1row)

感谢各位的阅读,以上就是“怎么使用PostgreSQL扩展函数”的内容了,经过本文的学习后,相信大家对怎么使用PostgreSQL扩展函数这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!