PostgreSQL10PostGIS相容性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，分別用於返回單行和多行。

如下

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