作者
digoal
日期
2017-09-27
标签
PostgreSQL , PostGIS , regexp_matches , _raster_constraint_info_blocksize
背景
PostGIS 的PG 10有一点兼容性问题：
创建extension时报错如下：
ERROR: set-returning functions are not allowed in CASE
LINE 6: split_part((regexp_matches(s...
^
HINT: You might be able to move the set-returning function into a LATERAL FROM item.
分析对应的.sql文件，出错的是这个FUNCTION。
CREATE OR REPLACE FUNCTION _raster_constraint_info_blocksize(rastschema name, rasttable name, rastcolumn name, axis text)
RETURNS integer AS $$
SELECT
CASE
WHEN strpos(s.consrc, 'ANY (ARRAY[') > 0 THEN
split_part((regexp_matches(s.consrc, E'ARRAY\\[(.*?){1}\\]'))[1], ',', 1)::integer -- 改成regexp_match
ELSE
regexp_replace(
split_part(s.consrc, '= ', 2),
'[\(\)]', '', 'g'
)::integer
END
FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
AND a.attrelid = c.oid
AND s.connamespace = n.oid
AND s.conrelid = c.oid
AND a.attnum = ANY (s.conkey)
AND s.consrc LIKE '%st_' || $4 || '(%= %';
$$ LANGUAGE sql STABLE STRICT
COST 100;
修正方法
regexp_matches改成regexp_match。
修正后的验证，正常返回约束即可。
```
postgres=# CREATE TABLE test_rast(rid serial, rast raster);
CREATE TABLE
postgres=#
postgres=# INSERT INTO test_rast(rast)
postgres-# SELECT r
postgres-# FROM ST_Tile(ST_MakeEmptyRaster(500, 500, 0,500, 1, 1, 0, 0, 0), 50, 50) AS r;
INSERT 0 100
postgres=#
postgres=# SELECT AddRasterConstraints(current_schema(), 'test_rast', 'rast'::name, 'blocksize');
NOTICE: Adding blocksize-X constraint
NOTICE: Adding blocksize-Y constraint
addrasterconstraints
t
(1 row)
postgres=# \d+ test_rast
Table "public.test_rast"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+----------------------------------------+----------+--------------+-------------
rid | integer | | not null | nextval('test_rast_rid_seq'::regclass) | plain | |
rast | raster | | | | extended | |
Check constraints:
"enforce_height_rast" CHECK (st_height(rast) = 50)
"enforce_width_rast" CHECK (st_width(rast) = 50)
postgres=# SELECT _raster_constraint_info_blocksize('public', 'test_rast', 'rast', 'width') AS width,
postgres-# _raster_constraint_info_blocksize('public', 'test_rast', 'rast', 'width') AS height;
width | height
-------+--------
50 | 50
(1 row)
```
扩展阅读
PostgreSQL 10新增了一个规则匹配函数regexp_match，分别用于返回单行和多行。
如下
Function| Return Type| Description| Example| Result
---|---|---|---|---
regexp_match(string text, pattern text [, flags text])| text[]| Return captured substring(s) resulting from the first match of a POSIX regular expression to the string. See Section 9.7.3 for more information.| regexp_match('foobarbequebaz', '(bar)(beque)')| {bar,beque}
regexp_matches(string text, pattern text [, flags text])| setof text[]| Return captured substring(s) resulting from matching a POSIX regular expression to the string. See Section 9.7.3 for more information.| regexp_matches('foobarbequebaz', 'ba.', 'g')| {bar} {baz} (2 rows)
10以前的版本如下，只有一个regexp_matches函数，效果与regexp_match类似。虽然返回的是SRF。
Function| Return Type| Description| Example| Result
---|---|---|---|---
regexp_matches(string text, pattern text [, flags text])| setof text[]| Return all captured substrings resulting from matching a POSIX regular expression against the string. See Section 9.7.3 for more information.| regexp_matches('foobarbequebaz', '(bar)(beque)')| {bar,beque}
因此PostgreSQL 10我们在PostGIS的postgis--2.3.2.sql中修正为regexp_match是可行的。
另一方面，实际上SRF函数在非SRF中调用并返回结果时，实际上返回的也是第一个匹配行，如下：
```
-- 非SRF函数中调用SRF。
postgres=# create or replace function f() returns int as $$
postgres$# select * from (values (1),(2),(3)) t(id);
postgres$# $$ language sql strict;
CREATE FUNCTION
postgres=# select f();
f
1
(1 row)
-- SRF函数中调用SRF。
postgres=# drop function f();
DROP FUNCTION
postgres=# create or replace function f() returns setof int as $$
select * from (values (1),(2),(3)) t(id);
$$ language sql strict;
CREATE FUNCTION
postgres=# select f();
f
1
2
3
(3 rows)
```
PostGIS插件安装中用到的一些字符串处理函数
PostGIS插件安装中用到了大量字符串处理函数，列举一些：
```
strpos
split_part
regexp_match
regexp_replace
```
例子
```
postgres=# select strpos('abc','a');
strpos
1
(1 row)
postgres=# select strpos('abc','b');
strpos
2
(1 row)
postgres=# select split_part('a.b.c','.',1);
split_part
a
(1 row)
postgres=# select split_part('a.b.c','.',2);
split_part
b
(1 row)
postgres=# select split_part('a.b.c','.',3);
split_part
c
(1 row)
postgres=# select regexp_match('foobarbequebaz', '(bar)(beque)');
regexp_match
{bar,beque}
(1 row)
postgres=# select regexp_matches('foobarbequebaz', 'ba.', 'g');
regexp_matches
{bar}
{baz}
(2 rows)
postgres=# select regexp_replace('Thomas', '.[mN]a.', 'M');
regexp_replace
ThM
(1 row)
```
参考
https://trac.osgeo.org/postgis/ticket/3760#no1
《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》
https://www.postgresql.org/docs/10/static/functions-string.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
