共计 13179 个字符，预计需要花费 33 分钟才能阅读完成。
这篇文章主要讲解了“怎么使用 PostgreSQL 扩展函数”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么使用 PostgreSQL 扩展函数”吧！
例子
. ./env11.sh
git clone https://github.com/MasaoFujii/pg_cheat_funcs
cd pg_cheat_funcs/
USE_PGXS=1 make
USE_PGXS=1 make installpostgres=# create extension pg_cheat_funcs ;
CREATE EXTENSION1、打印内存上下文
postgres=# select * from pg_stat_get_memory_context();
name | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | 0 | 312552 | 11 | 40520 | 21 | 272032
dynahash | TopMemoryContext | 1 | 8192 | 1 | 1456 | 0 | 6736
TopTransactionContext | TopMemoryContext | 1 | 8192 | 1 | 7744 | 1 | 448
PL/pgSQL function | TopMemoryContext | 1 | 16384 | 2 | 7176 | 1 | 9208
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 24528 | 2 | 2624 | 0 | 21904
dynahash | TopMemoryContext | 1 | 8192 | 1 | 2096 | 0 | 6096
dynahash | TopMemoryContext | 1 | 8192 | 1 | 1584 | 0 | 6608
dynahash | TopMemoryContext | 1 | 24576 | 2 | 10760 | 3 | 13816
RowDescriptionContext | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | TopMemoryContext | 1 | 32768 | 3 | 10904 | 1 | 21864
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 32768 | 3 | 16832 | 8 | 15936
TransactionAbortContext | TopMemoryContext | 1 | 32768 | 1 | 32512 | 0 | 256
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
TopPortalContext | TopMemoryContext | 1 | 8192 | 1 | 7664 | 0 | 528
PortalContext | TopPortalContext | 2 | 1024 | 1 | 592 | 0 | 432
ExecutorState | PortalContext | 3 | 49208 | 4 | 15576 | 3 | 33632
printtup | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256
Table function arguments | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256
ExprContext | ExecutorState | 4 | 8192 | 1 | 4536 | 0 | 3656
dynahash | TopMemoryContext | 1 | 16384 | 2 | 3512 | 2 | 12872
CacheMemoryContext | TopMemoryContext | 1 | 524288 | 7 | 20960 | 26 | 503328
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 1 | 1368
EventTriggerCache | CacheMemoryContext | 2 | 8192 | 1 | 7936 | 2 | 256
dynahash | EventTriggerCache | 3 | 8192 | 1 | 2624 | 0 | 5568
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
relation rules | CacheMemoryContext | 2 | 16384 | 5 | 7352 | 0 | 9032
index info | CacheMemoryContext | 2 | 2048 | 2 | 648 | 2 | 1400
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 592 | 3 | 1456
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424
index info | CacheMemoryContext | 2 | 2048 | 2 | 672 | 3 | 1376
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008
index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096
WAL record construction | TopMemoryContext | 1 | 49768 | 2 | 6368 | 0 | 43400
dynahash | TopMemoryContext | 1 | 8192 | 1 | 2624 | 0 | 5568
MdSmgr | TopMemoryContext | 1 | 8192 | 1 | 7432 | 0 | 760
dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632
dynahash | TopMemoryContext | 1 | 104120 | 2 | 2624 | 0 | 101496
ErrorContext | TopMemoryContext | 1 | 8192 | 1 | 7936 | 0 | 256
(121 rows)2、文本压缩
postgres=# select pglz_compress(repeat(md5(random()::text),1024)); pglz_compress
-------------------------------------------------------------------------------------------------------------------------------------
\x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f
20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f2
0ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f
20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7
(1 row)3、解压缩
pglz_decompress(bytea)
postgres=# select pglz_compress(repeat(md5(random()::text),8));
pglz_compress
--------------------------------------------------------------------------------------------
\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce
(1 row)
postgres=# select pglz_decompress( \x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce
pglz_decompress
------------------------------------------------------------------------------
26fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22
bcb0dbef8ac05641a897
(1 row)4、查看绑定变量 QUERY 的 PLAN COSE。
postgres=# prepare a (name) as select * from pg_class where relname=$1;
PREPARE
postgres=# select * from pg_cached_plan_source( a
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 0 | 0 | f | f
(1 row)
postgres=# execute a( abc
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source( a
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 4.61208554676785 | 1 | f | f
(1 row)
postgres=# execute a( abc
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source( a
generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom
--------------+-------------------+------------------+---------------+--------------
-1 | 9.22417109353571 | 2 | f | f
(1 row)感谢各位的阅读，以上就是“怎么使用 PostgreSQL 扩展函数”的内容了，经过本文的学习后，相信大家对怎么使用 PostgreSQL 扩展函数这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
正文完
