INSERT or UPDATE in PostgreSQL view
I start with PostgreSQL views as they are useful for my use case and 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 ok. I would like to write a Django wrapper around the view so that I can treat it like a table and also query and write it accordingly. I've been going through the
Postgres docs on INSERT
and off UPDATE
for views , but honestly, I find their docs so hard to read. I can barely make out what they are "Talking".
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 am using industry.slug
it listing.slug
to create links too. I would like to be able to update these two fields from this view:
UPDATE links
SET listing_slug = 'my-new-slug'
WHERE listing_id = 5;
How do you create rules to get it right?
source to share
Because of the double connection, it is better to use a trigger procedure. To update the industry table, you first need to find industry.id using the list of foreign keys - company-industry. The procedure might look like this:
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. The startup routine is a normal routine that returns TRIGGER. Depending on what the trigger does, the procedure should return NEW or OLD (NEW in this case).
And a trigger with a suggestion INSTEAD OF UPDATE
:
CREATE trigger update_view INSTEAD OF UPDATE ON links
FOR EACH ROW EXECUTE PROCEDURE update_listing_and_industry();
source to share
The only column in your view that can be updated is listing_slug
. Updating other columns is not possible or meaningless (for example, there is no point in updating industry_slug
because the view has no primary key for industry
). In such a case, you must use a conditional rule to ensure that no other columns can be updated.
As described in the documentation , it must be unconditional INSTEAD
for every action you want to resolve this point of view. Therefore, you must create a dummy rule INSTEAD
for the update and the conditional rule ALSO
:
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 add a column industry.id as industry_id
to the view, you can define an appropriate rule for the 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;
Typically, one rule is required per table ALSO
.
source to share