Postgres trigger that affects rows with certain values

Question

I'm postgres newbie and i have a question regarding triggers. Lets say we have 2 tables. One with cities and one with people.

CITIES:

PEOPLE:

I need a trigger that updates 'processed' value in table cities from 0 to 1 when 'processed' value in table people updates from 0 to 1 for all people from that city.

For example: John, Ian and Claire are from Berlin and when 'processed' value is updated to 1 for each of them, then the trigger updates processed value for Berlin in table cities to 1.

What is the best way to do this?

This seems to be the wrong way to structure the data. Just use a join to look to see whether the city has been processed. — Gordon Linoff
– Gordon Linoff, Commented Jun 13, 2017 at 11:23

alibaba · Accepted Answer · 2017-06-13 15:30:17Z

1

This will work:

CREATE OR REPLACE FUNCTION mytrigger() RETURNS TRIGGER AS $mytrigger$
    BEGIN
        IF (SELECT EXISTS (SELECT 1 FROM PEOPLE WHERE city_id = NEW.city_id AND processed = '0')) THEN
            UPDATE CITIES SET processed = '0' WHERE id = NEW.city_id;
            RETURN NEW;
        ELSE
            UPDATE CITIES SET processed = '1' WHERE id = NEW.city_id;
            RETURN NEW;
        END IF;
    END;
$mytrigger$ LANGUAGE plpgsql;

CREATE TRIGGER mytrigger
AFTER UPDATE ON PEOPLE
    FOR EACH ROW EXECUTE PROCEDURE mytrigger();

answered Jun 13, 2017 at 15:30

alibaba

1,9632 gold badges13 silver badges14 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2017-06-13 11:17:07Z

1

try this query:

with c as (
    select city_id
    from people
    where name = NEW.name
)
, a as (
select avg(processed) 
from people
join c on c.city_id = people.city_id
)
update cities u
set processed = 1
from a
where u.id = a.city_id
and avg = 1

alias c gets city id, then a aggregates processed for that sict id in people and lastly update updates only when all names are processed.

answered Jun 13, 2017 at 11:17

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres trigger that affects rows with certain values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related