PostgreSQL 10 PostGIS 兼容性 FIX

背景

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

《PostgreSQL 10.0 解读》

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

digoal’s 大量PostgreSQL文章入口