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?

Since it's not a simpy updatable view (it has joins) you'll need to write a DO INSTEAD rule or trigger. Rules are complicated and hard to get right, so I'd use a trigger. — Craig Ringer
– Craig Ringer, Commented Jul 20, 2015 at 4:17

Tom-db · Accepted Answer · 2015-07-21 08:22:08Z

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();

klin · Accepted Answer · 2015-07-20 12:10:38Z

The only column in your view, which can be updated is listing_slug. Updating the other columns is impossible or pointless (e.g. it makes no sense to update industry_slug as there is no primary key for industry in the view). In such a case you should use a conditional rule which precludes the ability to update other columns.

As it is described in the documentation, there must be an unconditional INSTEAD rule for each action you wish to allow on the view. Therefore you should create dummy INSTEAD rule for update and conditional ALSO rule:

CREATE RULE update_links_default
AS ON UPDATE TO links DO INSTEAD NOTHING;

CREATE RULE update_links_listing
AS ON UPDATE TO links 
WHERE NEW.listing_slug <> OLD.listing_slug
DO ALSO
    UPDATE listing 
    SET slug = NEW.listing_slug
    WHERE id = OLD.listing_id;

If you'll add column industry.id as industry_id to the view, you can define appropriate rule for table industry:

CREATE RULE update_links_industry
AS ON UPDATE TO links 
WHERE NEW.industry_slug <> OLD.industry_slug
DO ALSO
    UPDATE industry 
    SET slug = NEW.industry_slug
    WHERE id = OLD.industry_id;

Generally, you need one ALSO rule for a table.

Collectives™ on Stack Overflow

INSERT or UPDATE on PostgreSQL views

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related