I have a PostgreSQL database with the following tables
person
city
country
Each person is linked to a city through a city_id foreign key, and each city is linked to a country through country_id in a similar way.
To easily view the names of each person's city and country, I create a view:
CREATE VIEW person_view AS
SELECT
person.id,
person.name,
city.name as city,
country.name as country
FROM person
LEFT JOIN city ON person.city_id = city.id
LEFT JOIN country ON city.country_id = country.id
Which gives something easy to read.
| id | name | city | country |
------------------------------------------
| 1 | Steve | New York | United States |
| 2 | Rachel | Paris | France |
Now, using a program like dbeaver, I was hoping to manage these entries using this view. Instead of looking up IDs whenever a person's city/country needs to change, it'd be much easier just to type in changes in the view and have those changes carry over to the original tables.
I thought that this was what an updatable view was meant for, but dbeaver will not allow this view to be updated directly, and suggests implementing INSTEAD OF UPDATE triggers or ON UPDATE DO INSTEAD rules.
Am I approaching this correctly? Is the operation I've described here what updatable views are meant to do?
demo
Only show update trigger. You can do similar thing for insert and delete.
CREATE OR REPLACE FUNCTION person_view_upd_trig_fn ()
RETURNS TRIGGER
AS $$
BEGIN
IF tg_op = 'UPDATE' THEN
IF NEW.name <> OLD.name THEN
RAISE NOTICE 'update person name';
UPDATE
person
SET
name = NEW.name
WHERE
id = OLD.id;
RETURN new;
END IF;
IF NEW.city <> OLD.city AND NEW.country = OLD.country THEN
RAISE NOTICE 'update city name';
IF (
SELECT
count(DISTINCT country_id)
FROM
city
WHERE
name = OLD.city OR name = NEW.city) = 2 THEN
RAISE EXCEPTION 'not good';
END IF;
IF (
SELECT
city_id
FROM
city
WHERE
name = NEW.city) IS NULL THEN
RAISE EXCEPTION 'city not in the list';
END IF;
UPDATE
person
SET
city_id = (
SELECT
city_id
FROM
city
WHERE
name = NEW.city)
WHERE
id = OLD.id;
RETURN new;
END IF;
IF NEW.country <> OLD.country AND NEW.city <> OLD.city THEN
RAISE NOTICE 'updating person country & city';
IF NOT EXISTS (
SELECT
FROM
country
WHERE
name = NEW.country) THEN
RAISE EXCEPTION 'not good';
END IF;
UPDATE
person
SET
city_id = (
SELECT
city_id
FROM
city
WHERE
name = NEW.city)
WHERE
id = OLD.id;
RETURN new;
END IF;
RAISE NOTICE 'new.person_view:%', new;
RAISE NOTICE 'old.person_view:%', old;
RETURN NULL;
END IF;
END
$$
LANGUAGE plpgsql;
create trigger:
CREATE TRIGGER person_view_upd_trig
INSTEAD OF UPDATE ON person_view
FOR EACH ROW EXECUTE PROCEDURE person_view_upd_trig_fn();
person_view id column cannot update, update will have no effect. all other 3 column can update.
IF tg_op = 'UPDATE' THEN is not that redundant. You can add more control block, like IF tg_op = 'DELETE THEN in this function. Then one function, you can control 3 actions/trigger(delete, update, insert), instead of 3 function and 3 triggers.
https://www.postgresql.org/docs/current/plpgsql-trigger.html
A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.
and
INSTEAD OF triggers (which are always row-level triggers, and may only be used on views) can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT/UPDATE/DELETE). Otherwise a nonnull value should be returned, to signal that the trigger performed the requested operation. For INSERT and UPDATE operations, the return value should be NEW, which the trigger function may modify to support INSERT RETURNING and UPDATE RETURNING (this will also affect the row value passed to any subsequent triggers, or passed to a special EXCLUDED alias reference within an INSERT statement with an ON CONFLICT DO UPDATE clause). For DELETE operations, the return value should be OLD.