INSERT or UPDATE on PostgreSQL views

Question

I'm starting with PostgreSQL views, since they are useful for my use case and will provide better performance than functions.

(This isn't relevant, but I'm using Django 1.7 on Heroku Postgres—just in case).

I have already created a view and can query it fine. I'd like to write a Django wrapper around the view so I can treat it like a table, and query and write to it accordingly. I've been reviewing the Postgres docs on INSERT and UPDATE for views but to be honest I find their docs so hard to read I can barely parse through what they're saying.

Let's say I have the following view:

CREATE OR REPLACE VIEW links AS
  SELECT
    listing.id                                                     AS listing_id,
    CONCAT('/i-', industry.slug, '-j-', listing.slug, '/') AS link,
    'https://www.example.com' || CONCAT(industry.slug, '-SEP-', listing.slug, '/') AS full_link,
    listing.slug AS listing_slug,
    industry.slug AS industry_slug
  FROM listing
    INNER JOIN company ON company.id = listing.company_id
    INNER JOIN industry ON industry.id = company.industry_id

Here, I'm using industry.slug and listing.slug to build links. I'd like to be able to update those two fields from this view, as such:

UPDATE links
SET listing_slug = 'my-new-slug'
WHERE listing_id = 5;

How do I create the rules to do this properly?

Answer 1

Because of the double join, is better for you to use a trigger procedure. To update the industry table, you need first to find the industry.id using the foreign key listing-company-industry. A procedure could look like so:

CREATE OR REPLACE FUNCTION update_listing_and_industry() RETURNS TRIGGER AS
  $$
  DECLARE _company_id int; _industry_id int;
  BEGIN
    _company_id = (SELECT company_id FROM listing WHERE id = OLD.listing_id);
    _industry_id = (SELECT industry_id FROM company WHERE id = _company_id);

    UPDATE listing SET slug = NEW.listing_slug WHERE id = OLD.listing_id;
    UPDATE industry SET slug = NEW.industry_slug WHERE id = _industry_id;
    RETURN NEW;
  END;
  $$
LANGUAGE plpgsql;

NOTE: a trigger procedure is a normal procedure which returns a TRIGGER. Depending on what the trigger do, the procedure must return NEW or OLD (in this case NEW).

And the trigger with the INSTEAD OF UPDATE clause:

CREATE trigger update_view INSTEAD OF UPDATE ON links 
FOR EACH ROW EXECUTE PROCEDURE update_listing_and_industry();