Re: Problems With DELETE - Mailing list pgsql-general

 Hello List-

 I'm having some problems in my program that I've been spending the day
going over and over and am now going to ask for some help, as I cannot see
the problem, but am hoping some seasoned sets of eyes will!

 I have some VIEWS that need to get updated when the user chooses to delete
data from them from within my program.  Most views are working, but it seems
that one is not.

 Here's one that IS working:

 CREATE OR REPLACE VIEW
 "elementdata"."personnel_info"
 AS
 SELECT
 pi.projectname, pi.people_id,
 pi.elementid, p.last_name, p.first_name,
 p.job_title, p.business_email, p.business_phone,
 p.cell_phone, p.pager_no
 FROM
 elementdata.data_personnel_info pi
 INNER JOIN projectdata.people p USING (projectname, people_id);


 CREATE RULE
 delete_personnel_info
 AS ON DELETE TO
 elementdata.personnel_info
 DO INSTEAD DELETE FROM
 elementdata.data_personnel_info
 WHERE
 data_personnel_info.elementid = old.elementid
 AND
 data_personnel_info.people_id::text = old.people_id::text;


 CREATE RULE
 update_personnel_info
 AS ON UPDATE TO
 elementdata.personnel_info
 DO INSTEAD

 (
 --update data_personnel_info
 UPDATE
 elementdata.data_personnel_info
 SET
 projectname = new.projectname,
 people_id = new.people_id
 WHERE
 ((data_personnel_info.elementid = old.elementid)
 AND
 ((data_personnel_info.people_id)::text = (old.people_id)::text));

 --update shown projectdata.people fields
 UPDATE
 projectdata.people
 SET
 last_name = new.last_name,
 first_name = new.first_name,
 job_title = new.job_title,
 business_email = new.business_email,
 business_phone = new.business_phone,
 cell_phone = new.cell_phone,
 pager_no = new.pager_no
 WHERE
 (((people.projectname)::text = (old.projectname)::text)
 AND
 ((people.people_id)::text = (old.people_id)::text));

 );


 And now, for my 'measurement view', which is NOT working; when the user
chooses delete from the program, it's not deleting it from the database.
And if I run the statement in pgAdmin: delete from elementdata.measurement
where elementid = 18700; (a row in the measurement table of my database) -
it's telling me that it did something: "Query returned successfully: 1 rows
affected, 703 ms execution time.", but if I query the table based on that
elementid (select * from elementdata.measurement where elementid = 18700;),
it's still in the table...

 Here are my CREATE, DELETE, and UPDATE statements for the 'measurement'
view:

 Create view "elementdata"."measurement" As
 SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area * su.units_per_sqfoot AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot AS slopearea_sq, da.linear_unit AS
linear_unit_lin, (p.perimeter * lu.units_per_foot::double precision) AS
perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot
AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot 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));>


 CREATE OR REPLACE RULE
 delete_measurement
 AS ON DELETE TO
 elementdata.measurement
 DO INSTEAD DELETE FROM
 elementdata.data_measurement
 WHERE
 ((data_measurement.elementid = old.elementid)
 AND
 (element.elementid = old.elementid));


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



 Any thoughts/ideas would be greatly appreciated - I'm stuck as to why it's
working on all the views in the schema, but one...  If you need anymore info
from me, just let me know.

 Thanks in advance for your input and ideas!!
 -Jeanna