924      阿裏雲  技術社區[雲棲]

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

《PostgreSQL 使用遞歸SQL 找出數據庫對象之間的依賴關係》

《PostgreSQL 10.0 解讀》

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

最後更新：2017-10-28 23:03:56

  上一篇：  PostgreSQL 在鐵老大訂單係統中的schemaless設計和性能壓測

  下一篇：  PostgreSQL Oracle 兼容性 之 TABLE、PIPELINED函數