6.8 Triggers

6.53

create table cities (
    city text,
    country text,
    size_rank numeric,
    time_zone text,
    time_zone_abbrev text
)

6.54

create
or replace function set_timezones() returns trigger language plpgsql as $$ begin
update
    cities
set
    time_zone = a.name,
    time_zone_abbrev = a.abbrev
from
    pg_timezone_names a
where
    a.name like '%' || replace(city, ' ', ' _ ') || '%';

return new;

end;

$$

6.55

create trigger update_city_tz
after
insert
    on cities for each row execute procedure set_timezones();

6.56

insert into
    cities (city, country, size_rank)
values
    ('Tokyo', 'Japan', 1),
    ('Delhi', 'India', 2),
    ('Shanghai', 'China', 3),
    ('São Paulo', 'Brazil', 4),
    ('Mexico City', 'Mexico', 5)

6.57

create
or replace function set_timezones() returns trigger language plpgsql as $$ begin
update
    cities
set
    time_zone = data.name,
    time_zone_abbrev = data.abbrev
from
    (
        select
            name,
            abbrev
        from
            pg_timezone_names
        where
            name like '%' || replace(city, ' ', '_') || '%'
    ) as data;

return new;

end;

$$