# 修正四邊形
- DEBUG
https://www.pgadmin.org/docs/pgadmin4/development/debugger.html
- 目的:數化人員利用QGIS的表單輸入空間資料和屬性資料，postgis根據屬性資料修正其空間資料
### CREATE FUNCTION st_turnarray()

CREATE OR REPLACE FUNCTION ST_TurnArray(point_A geometry, point_B geometry,leng numeric,width numeric)
  RETURNS geometry AS
$BODY$
SELECT ST_Polygon(CONCAT('LINESTRING(',f.pAx,' ',f.pAy,',',((f.pBx-f.pAx)/f.dist)*$3+f.pAx,' ',((f.pBy-f.pAy)/dist)*$4+f.pAy,',',f.pAx+(2*cos(45)),' ',f.pAy+(2*sin(45)),',',f.pAx+(2),' ',f.pAy,',',f.pAx,' ',f.pAy,')')::geometry,3826)
FROM (SELECT
  ST_X($1) AS pAx, ST_Y($1) AS pAy,
  ST_X($2) AS pBx, ST_Y($2) AS pBy,
  ST_Distance($1,$2) AS dist
  FROM public.pk_mark0721) AS f
WHERE dist<>0
$BODY$
LANGUAGE sql IMMUTABLE;

##### USE FUNCTION st_turnarray() 確認函式是否可使用

SELECT
ST_AsText(st_turnarray(p1,p2,0.8,1.8))
FROM(SELECT
	ST_AsText(ST_PointN(ST_Boundary(geom),1)) AS p1,
	ST_AsText(ST_PointN(ST_Boundary(geom),2)) AS p2
	FROM public.pk_mark) A
WHERE ST_Distance(p1,p2)<>0;

### CREATE FUNCTION pk_mark_fixed()

CREATE OR REPLACE FUNCTION pk_mark_fixed() RETURNS trigger 
AS $$
BEGIN
  --UPDATE
      IF (TG_OP = 'UPDATE') THEN
        UPDATE public.pk_mark
        SET geom = st_turnarray(ST_AsText(ST_PointN(ST_Boundary(geom),1)),
				ST_AsText(ST_PointN(ST_Boundary(geom),2)),
				ST_AsText(ST_PointN(ST_Boundary(geom),3)),
				ST_AsText(ST_PointN(ST_Boundary(geom),4)),
				length,
				width);
      	END IF;
   	 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

### CREATE TRIGGER

CREATE OR REPLACE TRIGGER pk_mark_fixed 
AFTER UPDATE ON public.pk_mark
EXECUTE PROCEDURE public.pk_mark_fixed(); 

---
:::spoiler plpgsql寫法 看看就好~
- CREATE FUNCTION st_turnarray() ==USING plpgsql==

CREATE OR REPLACE FUNCTION ST_TurnArray(pa geometry,pb geometry,leng numeric(5,2),width numeric(5,2),OUT geom_fixed geometry)AS $$
DECLARE
  pa_x numeric(30,11) = ST_X(pa);
  pa_y numeric(30,11) = ST_Y(pa);
  pb_x numeric(30,11) = ST_X(pb);
  pb_y numeric(30,11) = ST_Y(pb);
  dist numeric(30,11)= ST_Distance($1,$2);
BEGIN
  geom_fixed := ST_Polygon(CONCAT('LINESTRING(',pa_x,' ',pa_y,',',
					((pb_x-pa_x)/dist)*leng+pa_x,' ',((pb_y-pa_y)/dist)*width+pa_y,',',
					pa_x+(2*cos(45)),' ',pa_y+(2*sin(45)),',',
					pa_x+(2),' ',pa_y,',',
					pa_x,' ',pa_y,')')::geometry,3826);
END;
$$
LANGUAGE plpgsql IMMUTABLE;

:::

:::spoiler trigger一些筆記
- PostgreSQL trigger can be specified(指定) to fire(觸發)
- Before:
- After:檢查完所設定定的限制條件後，再進行Insert、Update、Delete

CREATE [OR REPLACE] TRIGGER 觸發器名稱
觸發時間 觸發事件
ON 表名
[FOR EACH ROW]
BEGIN
PL/Pgsql 語句
END;

CREATE [OR PLACE] TRIGGER trigger_name
{AFTER|BEFORE} {INSERT|UPDATE|DELETE}
ON [schema.]table_name
[FOR EACH ROW]
[WHEN condition]

:::
###### tags: `postgreSQL`