Apply Rules to Enable Updates in Complex Views

Update Data Through a View Using a Rule

Write a PostgreSQL query to update a customer’s email via a view that uses a rule for update redirection.

Solution:

-- Update the customer's contact email through the CustomerView.
UPDATE CustomerView  -- The view is defined with rules to update the Customers table.
SET contact_email = '[email protected]'  -- Set the new email address.
WHERE customer_id = 200;  -- Identify the customer.

Explanation:

• Purpose of the Query:
• The goal is to update underlying table data via a view that is not inherently updatable by using a rule.
• This demonstrates how PostgreSQL rules can be created to handle INSERT, UPDATE, or DELETE operations on non-updatable views.
• Key Components:
• UPDATE CustomerView : Specifies the view that is made update-capable through a rule.
• SET contact_email = '[email protected]' : Modifies the email field.
• WHERE customer_id = 200 : Filters the target row.
• Real-World Application:
• Useful when complex views need to support DML operations without exposing direct table access.

Notes:

• The rule must be defined beforehand so that the UPDATE is redirected appropriately.
• Without such a rule, PostgreSQL would not permit the update on the view.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee’s position via a view that employs a rule to redirect the update to the underlying Employees table.
• Write a PostgreSQL query to update a customer’s phone number through a view that uses a rule to modify the underlying Customers table.
• Write a PostgreSQL query to update the discount rate via a view that leverages a rule to update the underlying Discounts table.
• Write a PostgreSQL query to update a supplier’s contact details through a view that uses a rule for redirecting the update to the Suppliers table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Attempt to Update a Non-Updatable Aggregated View.
Next PostgreSQL Exercise: Update Data Through a View with a Subquery Condition.