僅當某個變數為 TRUE 時才使用擴展連接子句

我正在使用 Postgis（對 postgres 的擴展），其中有幾種不同的幾何類型，即“多邊形”、“點”、“線串”以及這些的多變體。

我正在嘗試創建一個函式，如果幾何類型是（多）多邊形，則該函式應僅使用額外的連接子句。在所有情況下，對於所有幾何類型，連接應如下所示：

--drop function if exists test_return(geometry, double precision);
CREATE OR REPLACE FUNCTION test_return(
     PAR_geom                      geometry
    ,PAR_tolerance                 double precision DEFAULT 0.0000001
) RETURNS setof geometry
AS $$
DECLARE
   VAR_main_id int;
BEGIN

CASE ST_GeometryType(PAR_geom)
WHEN 'ST_LineString'  THEN
   VAR_main_id = 0;
WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
   VAR_main_id = 1;
WHEN 'ST_MultiPolygon' THEN 
   VAR_main_id = 2;
ELSE 
   raise EXCEPTION 'this function does not work on single-points';
END CASE;

return query
with repeated_pts as (
select (st_dumppoints(PAR_geom)).path, (st_dumppoints(PAR_geom)).geom 
where st_npoints(st_snaptogrid(PAR_geom, PAR_tolerance)) <> st_npoints(PAR_geom)
)   

select b.geom from 
repeated_pts a
inner join
repeated_pts b
on 

--- Here is how it should behave for all geometry-types---
a.path[1:VAR_main_id] = b.path[1:VAR_main_id] 
and 
a.path[array_upper(a.path, 1)]< b.path[array_upper(b.path, 1)] 
and 
st_dwithin(a.geom, b.geom, PAR_tolerance);
   END;
$$
LANGUAGE plpgsql;

如果它是一個（多）多邊形，我想用額外的連接子句擴展連接子句：

and
 
(
(
a.path[VAR_main_id] = 1 and 
a.path[array_upper(a.path, 1)] > 1 and 
b.path[array_upper(b.path, 1)] > 1
)
or
(
a.path[VAR_main_id] > 1 and 
b.path[array_upper(b.path, 1)] <= 4 
)
)

有沒有聰明的方法來做到這一點？

我知道可以將查詢插入到 case-when-then 結構中，如下所示：

case 
when st_geometrytype(PAR_geom) in ('ST_MultiPolygon', 'ST_Polygon')
then --- run query with additional join-clause -- 

else 
-- run query WITHOUT the extra join-clause --
end case;

但是，這種結構需要我兩次編寫查詢的第一部分 - 有沒有辦法避免這種情況？

如果它是一個（多）多邊形，我想用額外的連接子句擴展連接子句：

您所要求的可以通過JOIN子句中的普通布爾邏輯來實現。附加：

AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

 OR a.path[VAR_main_id] = 1
AND a.path[array_upper(a.path, 1)] > 1
AND b.path[array_upper(b.path, 1)] > 1

 OR a.path[VAR_main_id] > 1
AND b.path[array_upper(b.path, 1)] <= 4 
)

意思是，要麼類型不在列出的類型中，要麼('ST_MultiPolygon', 'ST_Polygon')必須滿足以下條件。

運算符優先級使得ANDbind before OR，所以我們不需要額外的括號。

該函式可能如下所示：

CREATE OR REPLACE FUNCTION test_return(par_geom      geometry
                                    , par_tolerance float8 = 0.0000001)
 RETURNS SETOF geometry
 LANGUAGE plpgsql AS
$func$
DECLARE
  var_main_id int;
BEGIN
  CASE ST_GeometryType(par_geom)
  WHEN 'ST_LineString'  THEN
      var_main_id = 0;
  WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
      var_main_id = 1;
  WHEN 'ST_MultiPolygon' THEN 
      var_main_id = 2;
  ELSE 
      RAISE EXCEPTION 'this function does not work on single-points';
  END CASE;

  RETURN QUERY
  WITH repeated_pts AS (
     SELECT d.path, d.geom 
     FROM   st_dumppoints(par_geom) d  -- evaluate once
     WHERE  st_npoints(st_snaptogrid(par_geom, par_tolerance)) <> st_npoints(par_geom)
  )   
  SELECT b.geom
  FROM   repeated_pts a
  JOIN   repeated_pts b ON a.path[1:var_main_id] = b.path[1:var_main_id] 
                       AND a.path[array_upper(a.path, 1)] < b.path[array_upper(b.path, 1)] 
                       AND st_dwithin(a.geom, b.geom, par_tolerance)
                       -- append here!
                       AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

                         OR a.path[VAR_main_id] = 1
                        AND a.path[array_upper(a.path, 1)] > 1
                        AND b.path[array_upper(b.path, 1)] > 1

                         OR a.path[VAR_main_id] > 1
                        AND b.path[array_upper(b.path, 1)] <= 4 
                       );
END
$func$

或許可以進一步簡化。我缺乏洞察力。

引用自：https://dba.stackexchange.com/questions/276431