When is a view updateable? The answer becomes important when using views to access the data via postgrest. If a view isn’t updateable then insert, update and delete operations will fail.

It’s possible to check by requesting ‘/’ from postgrest to get information about the endpoints available and looking at the insertable field.

[ {u'insertable': False, u'name': u'fruits', u'schema': u'public'}, {u'insertable': True, u'name': u'colours', u'schema': u'public'} ]

In the above, attempts to insert, update or delete from /colours will fail, but attempts for /fruits will be OK.

Simple Views

Where a view is nothing more than a select statement to return rows from a table, it should be updateable.

CREATE OR REPLACE VIEW colours AS SELECT * FROM data.colour;

Joins, Unions

Having joins or unions will require more work to make them updateable.

CREATE OR REPLACE VIEW fruits AS SELECT f.id, f.name, c.name as colour FROM data.fruit AS f INNER JOIN data.colour as c ON f.colour_id=c.id;

Due to the join, this view isn’t directly updateable.

Function & Trigger

In order to make it updateable a function is needed, together with a trigger to call it.

CREATE OR REPLACE FUNCTION insert_fruit() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE colour_id int; BEGIN SELECT id FROM data.colour WHERE name=NEW.colour INTO colour_id; INSERT INTO data.fruit (name, colour_id) VALUES (NEW.name, colour_id); RETURN NEW; END $$;

The trigger then tells postgresql to use the function when an insert is required.

CREATE TRIGGER fruit_action INSTEAD OF INSERT ON fruits FOR EACH ROW EXECUTE PROCEDURE insert_fruit();

Reviewing the endpoints now shows

[ {u'insertable': True, u'name': u'fruits', u'schema': u'public'}, {u'insertable': True, u'name': u'colours', u'schema': u'public'} ]

NB The insertable key refers ONLY to insert, so in this instance with only the insert function and trigger added update and delete operations will fail.

Inserting data is now as simple as 2 post requests.

POST /colours {"name": "green"} >> 201 POST /fruits {"name": "Apple", "colour": "green"} >> 201

Of course any attempt to update or delete will fail, despite having “insertable” set to True.

PATCH /fruits?name=eq.Apple {"name": "Green Apple"} >> 500 {"hint":"To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.", "details":"Views that do not select from a single table or view are not automatically updatable.", "code":"55000","message":"cannot update view "fruits""}

Update

The function required for updating a record is very similar to the insert one.

CREATE OR REPLACE FUNCTION update_fruit() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE colour_id int; BEGIN SELECT id FROM data.colour WHERE name=NEW.colour INTO colour_id; UPDATE data.fruit set name=NEW.name, colour_id=colour_id WHERE id=NEW.id; return NEW; END $$; CREATE TRIGGER fruit_action INSTEAD OF UPDATE ON fruits FOR EACH ROW EXECUTE PROCEDURE update_fruit();

PATCH /fruits?name=eq.Apple {"name": "Green Apple"} >> 204

NB It’s worth pointing out that every row matched will be updated, so be careful of the filter criteria provided on the URL.

Delete

The delete function needs to return the rows that it deletes. Note that while insert and update relied on NEW, delete uses OLD.

CREATE OR REPLACE FUNCTION delete_fruit() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN DELETE FROM data.fruit WHERE id=OLD.id; RETURN OLD; END $$; CREATE TRIGGER fruit_delete INSTEAD OF DELETE ON fruits FOR EACH ROW EXECUTE PROCEDURE delete_fruit();

With the final trigger in place, delete now works.

DELETE /fruits?id=eq.1 >> 204