文章目录
Writing Postgres Extensions - the Basics
-
first article
-
about
-
extending Pg through extensions.
-
the code examples here on branch part_i
-
https://github.com/adjust/postgresql_extension_demo/tree/part_i
base36
- You might already know the trick used by url shorteners.
- Use some unique random characters such as http://goo.gl/EAZSKW to point to something else.
- You have to remember what points to where, of course, so you need to store it in a database.
- But instead of saving 6 characters using varchar(6) (and thus wasting 7 bytes)
- why not use an integer with 4 bytes and
- represent it as base36?
The Extension Skeleton
- To be able to run the CREATE EXTENSION ,
- your extension needs at least two files
- control file in the format extension_name.control,
- tell Pg some basics about your extension
- a extension’s SQL script in the format extension–version.sql.
- add them into our project directory.
- our control file might be:
- base36.control
# base36 extension
comment = 'base36 datatype'
default_version = '0.0.1'
relocatable = true
- extension no functionality.
- add some in an SQL script file:
- base36–0.0.1.sql
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION base36" to load this file. \quit
CREATE FUNCTION base36_encode(digits int)
RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$
DECLARE
chars char[];
ret varchar;
val int;
BEGIN
chars := ARRAY[
'0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h',
'i','j','k','l','m','n','o','p','q','r','s','t', 'u','v','w','x','y','z'
];
val := digits;
ret := '';
WHILE val != 0 LOOP
ret := chars[(val % 36)+1] || ret;
val := val / 36;
END LOOP;
RETURN(ret);
END;
$$;
- second line ensures the file won’t be loaded into the database directly,only via CREATE EXTENSION.
- 这个简单的pl/pgsql函数允许将任何整数编码到它的base36表示形式中。
- 两文件复制到pg的SHAREDIR/extension中（可通过pg_config命令获取），就可通过CREATE EXTENSION使用这个扩展
- 但我们不会麻烦用户去弄清楚这些文件放在哪里，以及如何手动复制它们，这是makefile该做的。
- 现在项目中添加一个makefile。
Makefile
- 9.1开始，每个PostgreSQL安装都为扩展提供了一个名为PGXS的构建基础设施，允许在已经安装的服务器上轻松构建扩展。
- 构建扩展所需的大多环境变量都在pg_config中设置，可简单重用。
- 对于我们的示例，下面这个Makefile就符合我们的需求。
EXTENSION = base36 # 扩展名称
DATA = base36--0.0.1.sql # 要安装的脚本文件
# postgres build stuff
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
- 现在可开始用扩展了
- 在你的工程运行make install
- 并在数据库中执行如下
test=# CREATE EXTENSION base36;
CREATE EXTENSION
Time: 3,329 ms
test=# SELECT base36_encode(123456789);
base36_encode
---------------
21i3v9
(1 row)
Time: 0,558 ms