Re: Deleting From View?

Thanks for the quick reply; that's one of the many reasons why this list is
so awesome...

I'm taking this project over from somebody else and am new to the DB side of
things, so I'm still learning - you're responses and thoughts are greatly
appreciated!

Definition:

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));

DELETE measurement rule:

CREATE RULE delete_measurement AS ON DELETE
TO elementdata.measurement
DO INSTEAD UPDATE element SET height = 0 WHERE (element.elementid =
old.elementid);

UPDATE measurement rule:

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); );

Thanks again,
-Jeanna

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Martijn van
Oosterhout
Sent: Friday, January 05, 2007 9:47 AM
To: Jeanna Geier
Cc: pgsql-general
Subject: Re: [GENERAL] Deleting From View?

On Fri, Jan 05, 2007 at 09:42:09AM -0600, Jeanna Geier wrote:
>
> Hello List!
>
> I'm having an issue with my program; it's inserting into one record into a
> view (named 'measurement') twice ,which it's not supposed to be - so, I'm
> attempting to put a delete statement in the code to remove one of these
> records, but am getting an error. (Will track down the real insert issue
> when I have more time, right now I just need to get this compiling....)

Normally you can't insert or delete from a view, because a view doesn't
exist, it's the result of a query. Obviously you have some rules setup
to allow inserting, which is obviously not doing the right thing if the
output suddenly creates two rows.

You're going to need to provide the complete definition of the
table+rules if you want a more detailed answer.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
litigate.