PostgreSQL触发函数开发求助：表B更新时按ST_Within条件更新表A

Hey there! Let's work through this trigger function issue together—PostGIS triggers can be tricky when it comes to spatial conditions, but we'll get it sorted. Based on your setup (PostgreSQL 10 + PostGIS 2.4), here's a step-by-step solution and troubleshooting guide:

Let's assume your tables look something like this (adjust to match your actual schema):

• Table A (point data): id SERIAL PRIMARY KEY, geom GEOMETRY(Point, 4326), b_assoc_id INT, target_column TEXT
• Table B (polygon data): id SERIAL PRIMARY KEY, geom GEOMETRY(Polygon, 4326), polygon_attr TEXT

This function handles all three modification types (INSERT/UPDATE/DELETE) on Table B, and updates Table A records that meet the ST_Within spatial condition:

CREATE OR REPLACE FUNCTION sync_a_with_b()
RETURNS TRIGGER AS $$
BEGIN
    -- Handle INSERT/UPDATE on Table B: Update Table A records inside the modified polygon
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        UPDATE table_a
        SET b_assoc_id = NEW.id, target_column = NEW.polygon_attr
        WHERE ST_Within(table_a.geom, NEW.geom);
    END IF;

    -- Handle DELETE on Table B: Clear associations in Table A for records that were inside the deleted polygon
    IF TG_OP = 'DELETE' THEN
        UPDATE table_a
        SET b_assoc_id = NULL, target_column = NULL
        WHERE ST_Within(table_a.geom, OLD.geom);
    END IF;

    RETURN NULL; -- For AFTER triggers, return value doesn't affect execution
END;
$$ LANGUAGE plpgsql;

Bind the function to Table B so it fires whenever the table is modified:

CREATE TRIGGER trigger_b_modified_sync_a
AFTER INSERT OR UPDATE OR DELETE ON table_b
FOR EACH ROW EXECUTE FUNCTION sync_a_with_b();

If your original attempt didn't work, check these common issues:

• Mismatched Spatial Reference IDs (SRID): Ensure both table_a.geom and table_b.geom use the same SRID (e.g., 4326). Verify with SELECT ST_SRID(geom) FROM table_a LIMIT 1;—if mismatched, use ST_Transform to align them.
• Wrong Trigger Timing: Using BEFORE instead of AFTER means the modified polygon data isn't yet persisted, so the spatial check will fail. Stick with AFTER for this use case.
• Missing Spatial Indexes: For large datasets, missing GIST indexes on the geom columns will make the ST_Within check slow or time out. Add them with:

  CREATE INDEX idx_table_a_geom ON table_a USING GIST(geom);
  CREATE INDEX idx_table_b_geom ON table_b USING GIST(geom);
  

• Boundary Edge Cases: ST_Within excludes points exactly on polygon boundaries. If you need to include these, replace ST_Within with ST_Covers instead.
• Permission Issues: Make sure the user executing the trigger has SELECT and UPDATE permissions on both tables.

内容的提问来源于stack exchange，提问作者Boodoo