Hello List!
I'm still on the upwards learning curve with Postgres and this is my first
exposure to CREATE RULE....
I'm using Postgres 8.0. First, let me describe how we're using it; we have
a view (measurement) that displays to the user the measurement of a selected
room on a drawing. This is working OK. However, on this view, there is a
drop-down box for the unit of measurement to display the details (i.e.:
feet, centimeters, meters, inches, etc). Now, here's my problem, whenever
the user tries to select something else from the drop-down other than the
default (feet), the following message is being displayed: "Error updating
record in database: ERROR: cannot update a view."
Here is my elementdata.measurement View (as stored in Case Studio):
Create view "elementdata"."measurement" As
SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area::integer * su.units_per_sqfoot::integer AS area_sq, e.slope::integer
AS slope_inches, sa.slopearea::integer * cu.units_per_cufoot::integer AS
slopearea_sq, da.linear_unit AS linear_unit_lin, (p.perimeter *
lu.units_per_foot::double precision)::integer AS perimeter_lin,
da.cubic_unit AS cubic_unit_cu, e.height::integer *
lu.units_per_foot::integer AS height_lin, e.height::double
precision::integer * a.area::integer * cu.units_per_cufoot::integer AS
volume_cu, da.drawingid
FROM
((((((((((SELECT perimeter.elementid, perimeter.perimeter FROM
elementdata.perimeter
UNION
SELECT length.elementid, length.length AS perimeter FROM
elementdata.length)
UNION
SELECT circumference.elementid, circumference.circumference AS perimeter
FROM elementdata.circumference) p
LEFT JOIN elementdata.area a USING (elementid))
LEFT JOIN element e USING (elementid))
LEFT JOIN elementdata.slopearea sa USING (elementid))
JOIN layer la USING (layerid))
JOIN drawing da USING (drawingid))
JOIN globaldata.linear_units lu USING (linear_unit))
JOIN globaldata.square_units su USING (square_unit))
JOIN globaldata.cubic_units cu USING (cubic_unit));
----------------------------------------------------------------------------
-----------------------------
And here is the update_measurement RULE (as stored in Case Studio):
CREATE RULE update_measurement AS ON UPDATE
TO elementdata.measurement
DO INSTEAD (UPDATE element SET height = (new.height_lin / (SELECT
linear_units.units_per_foot FROM globaldata.linear_units WHERE
((linear_units.linear_unit)::text =
(old.linear_unit_lin)::text))), slope =
new.slope_inches WHERE (element.elementid = old.elementid); UPDATE drawing
SET linear_unit = new.linear_unit_lin, square_unit =
new.square_unit_sq, cubic_unit =
new.cubic_unit_cu WHERE (drawing.drawingid = old.drawingid); );
----------------------------------------------------------------------------
-----------------------------
Where/why is the exception being thrown that the view cannot be updated?
How can I fix this?? Any help or guidance that anyone could provide will be
greatly appreciated!
If any more information is necessary from my end, please let me know.
Thanks,
-Jeanna