CREATE TABLE label_point (
gid serial NOT NULL,
geom geometry(point, SRID),
label_sample varchar(255),
CONSTRAINT label_point_pkey PRIMARY KEY (gid)
);

CREATE TABLE soil (
gid serial NOT NULL,
geom geometry(polygon, SRID),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);

CREATE OR REPLACE FUNCTION sample_label()
RETURNS trigger AS $body$
    BEGIN
    IF GeometryType(NEW.geom) = 'POINT' THEN
        EXECUTE 'SELECT label FROM soil WHERE ST_Within($1, soil.geom) LIMIT 1'
        USING NEW.geom
        INTO NEW.label_sample;
        RETURN NEW;
    ELSEIF GeometryType(NEW.geom) = 'POLYGON' THEN
        EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;
        RETURN NEW;
    END IF;
    END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER tg_sample_label BEFORE INSERT OR UPDATE
ON label_point FOR EACH ROW
EXECUTE PROCEDURE sample_label();


CREATE TRIGGER tg_sample_label AFTER INSERT OR UPDATE
ON soil FOR EACH ROW
EXECUTE PROCEDURE sample_label();