Trigger Computed Changes by Updating Base Data in a View

Update a View with Computed Columns by Modifying Base Data

Write a PostgreSQL query to update underlying data through a view that includes computed columns.

Solution:

-- Update the salary via the EmployeeBonusView; bonus is computed as 10% of salary.
UPDATE EmployeeBonusView  -- The view displays salary and computed bonus.
SET salary = salary + 1000  -- Increase the salary, which will also change the computed bonus.
WHERE employee_id = 105;  -- Identify the employee.

Explanation:

• Purpose of the Query:
• The goal is to update the base column (salary) through a view that also calculates a bonus. o This demonstrates that computed columns (like bonus) are recalculated automatically when base data changes.
• Key Components:
• UPDATE EmployeeBonusView : Specifies the view that includes a computed bonus column.
• SET salary = salary + 1000 : Modifies the base salary.
• WHERE employee_id = 105 : Targets a specific employee.
• Real-World Application:
• Useful for financial adjustments where computed values (e.g., bonus, tax) update dynamically.

Notes:

• The computed column is not stored but derived on the fly based on the updated data.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee's base salary through a view that computes a tax deduction column dynamically.
• Write a PostgreSQL query to update the base cost of a product using a view that calculates discount and tax values on the fly.
• Write a PostgreSQL query to update a student's raw score via a view that automatically recalculates the final grade using computed bonus points.
• Write a PostgreSQL query to update a product's base price through a view that derives a computed profit margin column from the updated value.

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

Previous PostgreSQL Exercise: Update Data through a View with Column Aliases.
Next PostgreSQL Exercise: Update Data through a Partitioned View.