PostgreSQL 10 PostGIS 兼容性 FIX

标签

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，分别用于返回单行和多行。

如下

10以前的版本如下，只有一个regexp_matches函数，效果与regexp_match类似。虽然返回的是SRF。

因此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 找出数据库对象之间的依赖关系》

《PostgreSQL 10.0 解读》

https://www.postgresql.org/docs/10/static/functions-string.html