postgrest views

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