Need help writing a PostgreSQL trigger function

Question

I have two tables representing two different types of imagery. I am using PostGIS to represent the boundaries of those images. Here is a simplified example of those tables:

CREATE TABLE img_format_a (
    id SERIAL PRIMARY KEY,
    file_path VARCHAR(1000),
    boundary GEOGRAPHY(POLYGON, 4326)
);

CREATE TABLE img_format_p (
    id SERIAL PRIMARY KEY,
    file_path VARCHAR(1000),
    boundary GEOGRAPHY(POLYGON, 4326)
);

I also have a cross reference table, which I want to contain all the IDs of the images that overlap each other. Whenever an image of type "A" gets inserted into the database, I want to check to see whether it overlaps any of the existing imagery of type "P" (and vice versa) and insert corresponding entries into the img_a_img_p cross reference table. This table should represent a many-to-many relationship.

My first instinct is to write a trigger to manage thisimg_a_img_p table. I've never created a trigger before, so let me know if this is a silly thing to do, but it seems to make sense to me. So I create the following trigger:

CREATE TRIGGER update_a_p_cross_reference
    AFTER INSERT OR DELETE OR UPDATE OF boundary
    ON img_format_p FOR EACH ROW
    EXECUTE PROCEDURE check_p_cross_reference();

The part where I am getting stuck is with writing the trigger function. My code is in Java and I see that there are tools like PL/pgSQL, but I'm not sure if that's what I should use or if I even need one of those special add-ons.

Essentially all I need the trigger to do is update the cross reference table each time a new image gets inserted into either img_format_a or img_format_p. When a new image is inserted, I would like to use a PostGIS function like ST_Intersects to determine whether the new image overlaps with any of the images in the other table. For each image pair where ST_INTERSECTS returns true, I would like to insert a new entry into img_a_img_p with the ID's of both images. Can someone help me figure out how to write this trigger function? Here is some pseudocode:

SELECT * FROM img_format_p P
    WHERE ST_Intersects(A.boundary, P.boundary);

for each match in selection {
    INSERT INTO img_a_img_p VALUES (A.id, P.id);
}

Answer 1

You could wrap the usual INSERT ... SELECT idiom in a PL/pgSQL function sort of like this:

create function check_p_cross_reference() returns trigger as
$$
begin
    insert into img_a_img_p (img_a_id, img_p_id)
    select a.id, p.id
    from img_format_a, img_format_p
    where p.id = NEW.id
      and ST_Intersects(a.boundary, p.boundary);
    return null;
end;
$$ language plpgsql;

Triggers have two extra variables, NEW and OLD:

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

So you can use NEW.id to access the new img_format_p value that's going in. You (currently) can't use the plain SQL language for triggers:

It is not currently possible to write a trigger function in the plain SQL function language.

but PL/pgSQL is pretty close. This would make sense as an AFTER INSERT trigger:

CREATE TRIGGER update_a_p_cross_reference
AFTER INSERT
ON img_format_p FOR EACH ROW
EXECUTE PROCEDURE check_p_cross_reference();

Deletes could be handled with a foreign key on img_a_img_p and a cascading delete. You could use your trigger for UPDATEs as well:

CREATE TRIGGER update_a_p_cross_reference
AFTER INSERT OR UPDATE OF boundary
ON img_format_p FOR EACH ROW
EXECUTE PROCEDURE check_p_cross_reference();

but you'd probably want to clear out the old entries before inserting the new ones with something like:

delete from img_a_img_p where img_p_id = NEW.id;

before the INSERT...SELECT statement.