Thread: Re: Deleting From View?
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....)
Code:
if (tableName.equalsIgnoreCase("measurement")){
deleteRow(1);
}
And when I run my program I get an exception: cannot delete from a view.
Is there anyway I can do this in my code?
I'm running Postgres 8.0 & Java JDK1.5.
Thanks in advance for your time and help.
-Jeanna
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Jeanna Geier <jgeier@apt-cafm.com> schrieb: > > 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 You can't insert data into a view. My guess: you have a RULE for this VIEW to handle INSERTs. > 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 > ... > And when I run my program I get an exception: cannot delete from a view. > > Is there anyway I can do this in my code? You need a RULE for DELETE, too. Take a look at http://www.postgresql.org/docs/8.1/interactive/rules-update.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
litigate.
On Fri, Jan 05, 2007 at 09:59:34AM -0600, Jeanna Geier wrote: > 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! You didn't post the INSERT rule. However, what's truly interesting is that you do have a delete rule, yet you're getting an exception. Which indicates the exception is being generated from within java, and not by postgresql at all. Obviously, whatever trick that was applied to convince java to insert into a view needs to be used to make the delete work. Wouldn't fixing the insert rule be better? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Interestingly enough... he doesn't have an INSERT rule... The INSERTs all appear to be done using the JDBC and prepared statements in the code. -----Original Message----- From: Martijn van Oosterhout [mailto:kleptog@svana.org] Sent: Friday, January 05, 2007 10:16 AM To: Jeanna Geier Cc: pgsql-general Subject: Re: [GENERAL] Deleting From View? On Fri, Jan 05, 2007 at 09:59:34AM -0600, Jeanna Geier wrote: > 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! You didn't post the INSERT rule. However, what's truly interesting is that you do have a delete rule, yet you're getting an exception. Which indicates the exception is being generated from within java, and not by postgresql at all. Obviously, whatever trick that was applied to convince java to insert into a view needs to be used to make the delete work. Wouldn't fixing the insert rule be better? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Fri, Jan 05, 2007 at 10:22:38AM -0600, Jeanna Geier wrote: > Interestingly enough... he doesn't have an INSERT rule... > > The INSERTs all appear to be done using the JDBC and prepared statements in > the code. Well, I think that's going to make your job difficult then. Obviously whatever is happening, the insert code is doing more than expected. Remember, a view is the result of a query, it's not hard to create views where you can't do meaningful DELETE rules. In your example you're joining many tables. If any of those tables have a duplicate value, that would duplicate records in the result. You can't delete any particular duplicate because it is the result, not the cause. Hope this helps, > -----Original Message----- > From: Martijn van Oosterhout [mailto:kleptog@svana.org] > Sent: Friday, January 05, 2007 10:16 AM > To: Jeanna Geier > Cc: pgsql-general > Subject: Re: [GENERAL] Deleting From View? > > > On Fri, Jan 05, 2007 at 09:59:34AM -0600, Jeanna Geier wrote: > > 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! > > You didn't post the INSERT rule. > > However, what's truly interesting is that you do have a delete rule, > yet you're getting an exception. Which indicates the exception is being > generated from within java, and not by postgresql at all. Obviously, > whatever trick that was applied to convince java to insert into a view > needs to be used to make the delete work. > > Wouldn't fixing the insert rule be better? > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > > From each according to his ability. To each according to his ability to > litigate. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.