PostgreSQL UDF妙用 - mybatis等框架，不支持的语法都可以通过UDF来实现
背景
PostgreSQL 的功能非常的强大，以至于一些框架可能无法完全发挥出PG的功能。
不过现在活跃的框架对PG的支持越来越好了，例如Django，就有专门针对PG的模块，可以使用PG的JSON，索引接口等功能。
https://docs.djangoproject.com/en/2.0/search/?q=postgresql
那么在框架不支持某些PG特性时，有什么方法让它支持起来呢？
答案是UDF，没错调用UDF就可以用到这些特殊的功能了。
打个比方，PG支持9种索引，语法如下：
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap…)的方法》
《Greenplum 最佳实践 - 什么时候选择bitmap索引》
而使用框架时，创建索引也许没有USING method的接口。
使用UDF就可以解决这个问题。
因为select function($parameter)所有框架都支持。
例子1
create or replace function f_crt_idx(name,name,name,name,text,name) returns void as $$
declare
sql text := format('create index %I on %I.%I using %s (%s) tablespace %s', $1,$2,$3,$4,$5,$6);
begin
raise notice '%', sql;
execute sql;
end;
$$ language plpgsql strict;
postgres=# select f_crt_idx('idx_a', 'public', 'a', 'btree', 'c1,c2', 'pg_default');
NOTICE: create index idx_a on public.a using btree (c1,c2) tablespace pg_default
f_crt_idx
-----------
(1 row)
例子2
批量upsert写入，函数如下
create or replace function upsert_batch(text) returns void as $$
declare
begin
execute format('insert into tbl values %s on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', $1);
end;
$$ language plpgsql strict;
测试
create table tbl(id int primary key, info text, crt_time timestamp);
postgres=# select upsert_batch($$(1,'test','2018-01-01'),(2,'test','2018-01-01'),(3,'test','2018-01-01'),(4,'test','2018-01-01')$$);
upsert_batch
--------------
(1 row)
postgres=# select ctid,* from tbl;
ctid | id | info | crt_time
-------+----+------+---------------------
(0,1) | 1 | test | 2018-01-01 00:00:00
(0,2) | 2 | test | 2018-01-01 00:00:00
(0,3) | 3 | test | 2018-01-01 00:00:00
(0,4) | 4 | test | 2018-01-01 00:00:00
(4 rows)
postgres=# select upsert_batch($$(1,'test','2018-01-01'),(2,'test','2018-01-01'),(3,'test','2018-01-01'),(4,'test','2018-01-01')$$);
upsert_batch
--------------
(1 row)
postgres=# select ctid,* from tbl;
ctid | id | info | crt_time
-------+----+------+---------------------
(0,5) | 1 | test | 2018-01-01 00:00:00
(0,6) | 2 | test | 2018-01-01 00:00:00
(0,7) | 3 | test | 2018-01-01 00:00:00
(0,8) | 4 | test | 2018-01-01 00:00:00
(4 rows)
通用批量UPSERT函数
create or replace function upsert_batch(text,text,text) returns void as $$
declare
begin
execute format('%s %s %s', $1, $2, $3);
end;
$$ language plpgsql strict;
测试
select upsert_batch(
'insert into tbl values',
$$(1,'test','2018-01-01'),(2,'test','2018-01-01'),(3,'test','2018-01-01'),(4,'test','2018-01-01')$$,
'on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time'
);
upsert_batch
--------------
(1 row)
postgres=# select ctid,* from tbl;
ctid | id | info | crt_time
--------+----+------+---------------------
(0,9) | 1 | test | 2018-01-01 00:00:00
(0,10) | 2 | test | 2018-01-01 00:00:00
(0,11) | 3 | test | 2018-01-01 00:00:00
(0,12) | 4 | test | 2018-01-01 00:00:00
(4 rows)
postgres=# select upsert_batch(
'insert into tbl values',
$$(1,'test','2018-01-01'),(2,'test','2018-01-01'),(3,'test','2018-01-01'),(4,'test','2018-01-01')$$,
'on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time'
);
upsert_batch
--------------
(1 row)
postgres=# select ctid,* from tbl;
ctid | id | info | crt_time
--------+----+------+---------------------
(0,13) | 1 | test | 2018-01-01 00:00:00
(0,14) | 2 | test | 2018-01-01 00:00:00
(0,15) | 3 | test | 2018-01-01 00:00:00
(0,16) | 4 | test | 2018-01-01 00:00:00
(4 rows)
小结
使用UDF，可以实现几乎所有的功能接口，使得任何框架都可以支持PG的所有功能。
参考
《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap…)的方法》
《Greenplum 最佳实践 - 什么时候选择bitmap索引》