招聘网站开发价格仿163ym源码交易平台网站源码

摘要： 标签 PostgreSQL , 多重函数数组 , UDF索引 , 过滤 , 文本处理 背景 PG的数组类型，被广泛应用于 画像系统 ， 标签系统。 在一些业务重建中，对数组内容的定义往往包含了多重含义，例如即包含了标签本身，又包含了标签的属性（例如 标签值:权值,时间 等）。

点此查看原文

标签 
PostgreSQL , 多重函数数组 , UDF索引 , 过滤 , 文本处理

背景 
PG的数组类型，被广泛应用于 画像系统 ， 标签系统。

在一些业务重建中，对数组内容的定义往往包含了多重含义，例如即包含了标签本身，又包含了标签的属性（例如 标签值:权值,时间 等）。

那么如何能高效的进行标签的检索，同时又过滤出符合标签加权值的记录呢？

例子 
1、建表

create table tbl(id int, info text[]); 
2、写入测试数据

insert into tbl values (1, array[‘a:100’, ‘b:10’]);

insert into tbl values (2, array[‘a:15’, ‘b:20’, ‘c:99’]);

insert into tbl values (3, array[‘c:78’, ‘b:100’]);

postgres=# select * from tbl; 
id | info 
—-+—————— 
1 | {a:100,b:10} 
2 | {a:15,b:20,c:99} 
3 | {c:78,b:100} 
(3 rows) 
3、创建UDF1，提取出要查询的标签值（用到了正则匹配）

create or replace function get_label(text[]) returns text[] as

language sql strict immutable;

postgres=# select get_label(info) from tbl;

get_label

{a,b} 
{a,b,c} 
{c,b} 
(3 rows) 
4、创建UDF1索引

create index idx_tbl1 on tbl using gin (get_label(info));

postgres=# explain select * from tbl where get_label(info) @> array[‘a’];

QUERY PLAN

Bitmap Heap Scan on tbl (cost=2.40..3.86 rows=1 width=36) 
Recheck Cond: (get_label(info) @> ‘{a}’::text[]) 
-> Bitmap Index Scan on idx_tbl1 (cost=0.00..2.40 rows=1 width=0) 
Index Cond: (get_label(info) @> ‘{a}’::text[]) 
(4 rows) 
5、创建UDF2，提取指定标签的加权值（用到了正则匹配，数组下标计算，数组按位置取元素等操作）

create or replace function get_weight(text[], text) returns text as

language sql strict immutable;

postgres=# select info, get_weight(info, ‘a’) from tbl; 
info | get_weight 
——————+———— 
{a:100,b:10} | 100 
{a:15,b:20,c:99} | 15 
{c:78,b:100} | 
(3 rows) 
6、查询SQL如下

查询包含标签a，同时权值大于20的记录。

postgres=# select * from tbl where get_label(info) @> array[‘a’] and get_weight(info, ‘a’)::float8 >20; 
id | info 
—-+————– 
1 | {a:100,b:10} 
(1 row)

postgres=# explain select * from tbl where get_label(info) @> array[‘a’] and get_weight(info, ‘a’)::float8 >20;

QUERY PLAN

Bitmap Heap Scan on tbl (cost=2.40..4.12 rows=1 width=36) 
Recheck Cond: (get_label(info) @> ‘{a}’::text[]) 
Filter: ((get_weight(info, ‘a’::text))::double precision > ‘20’::double precision) 
-> Bitmap Index Scan on idx_tbl1 (cost=0.00..2.40 rows=1 width=0) 
Index Cond: (get_label(info) @> ‘{a}’::text[]) 
(5 rows) 
UDF功能是不是很赞呢？

UPSERT时，如何修改数组、追加数组元素 
https://www.postgresql.org/docs/10/static/functions-array.html

1、追加元素

array_append(anyarray, anyelement)

array_cat(anyarray, anyarray)

array_fill(anyelement, int[], [, int[]])

array_prepend(anyelement, anyarray) 
2、修改元素

array_replace(anyarray, anyelement, anyelement) 
3、删除元素

array_remove(anyarray, anyelement) 
用法举例

insert into tbl values (1, ?) on conflict (id) do update set info=func(tbl.info,?); 
create table tbl1(id int primary key, info int[]);

postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *; 
id | info 
—-+——— 
1 | {1,2,3} 
(1 row)

INSERT 0 1 
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *; 
id | info 
—-+————- 
1 | {1,2,3,100} 
(1 row)

INSERT 0 1 
postgres=# insert into tbl1 values (1, null) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *; 
id | info 
—-+—————– 
1 | {1,2,3,100,100} 
(1 row) 
INSERT 0 1

扫描二维码获取更多消息：