Help with RULES - Please! - Mailing list pgsql-jdbc

From Jeanna Geier
Subject Help with RULES - Please!
Date
Msg-id FBEGJLLJBCOMCDBJHIMEAEEDCCAA.jgeier@apt-cafm.com
Whole thread Raw
Responses Re: Help with RULES - Please!
Re: Help with RULES - Please!
List pgsql-jdbc
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


pgsql-jdbc by date:

Previous
From: Tom Lane
Date:
Subject: Re: Support for DatabaseMetadata: getCatalogName, getTableName, getColumnLabel
Next
From: Kevin Murphy
Date:
Subject: ClassCastException DelegatingPreparedStatement