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);
}

mu is too short · Accepted Answer · 2011-08-10 17:20:04Z

4

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.

edited Aug 10, 2011 at 17:20

answered Aug 5, 2011 at 21:04

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steph Over a year ago

Wow, thank you for such an awesome and detailed answer. I appreciate it!

Steph Over a year ago

When I run your trigger function, I get this error: ERROR: syntax error at or near "insert" LINE 3: insert into img_a_img_p (img_a_id, img_p_id). Do you know why I would get this? PL/pgSQL should be installed by default for spatial databases, right? I'm having trouble understanding this error.

mu is too short Over a year ago

@Steph: That's because I missed a "begin" in the procedure (see the new third line). Sorry, I just wrote that off the top of my head without testing it.

Steph Over a year ago

Ah, that fixed it. Sorry, I should have been able to figure that one out. :-P

Collectives™ on Stack Overflow

Need help writing a PostgreSQL trigger function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related