Problems With VIEWS

From: "Jeanna Geier" <jgeier(at)apt-cafm(dot)com>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Problems With VIEWS
Date: 2007-01-10 21:16:04
Message-ID: FBEGJLLJBCOMCDBJHIMECENJCCAA.jgeier@apt-cafm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello List-

I'm having some problems with Views that I'd really appreciate some feedback
and ideas on...

I have a Java program that we're developing that's a CAD/blueprint program
that allows the user to select a room in a building and attach information
from the database about that room (i.e. age, capacity, flooring, measurement
info, etc.). OK, so whenever the info is attached to a TABLE, no problems;
but, when it's attached to a VIEW that's been created, that's when there's
issues.

For example, I'm creating a VIEW called age_information:

CREATE OR REPLACE VIEW
elementdata.age_information
AS
SELECT
data_age_information.elementid,
data_age_information.projectname,
data_age_information.original_date,
(((date_part('year'::text,
age((data_age_information.original_date)::timestamp
with time zone)) * (12)::double precision)
+ date_part('month'::text,
age((data_age_information.original_date)::timestamp
with time zone))) / (12)::double precision) AS age_yrs,
data_age_information.upgrade_date,
(((date_part('year'::text,
age((data_age_information.upgrade_date)::timestamp
with time zone)) * (12)::double precision)
+ date_part('month'::text,
age((data_age_information.upgrade_date)::timestamp
with time zone))) / (12)::double precision) AS upgrade_age_yrs
FROM
elementdata.data_age_information
;

The VIEW is being created without any problems. I also have DELETE, INSERT,
and UPDATE rules for this VIEW as well:

CREATE RULE delete_age_info AS ON DELETE
TO elementdata.age_information
DO INSTEAD DELETE FROM elementdata.data_age_information WHERE
(data_age_information.elementid = old.elementid);

CREATE RULE insert_age_info AS ON INSERT
TO elementdata.age_information
DO INSTEAD INSERT INTO elementdata.data_age_information (elementid,
projectname, original_date, upgrade_date) VALUES
(new.elementid, new.projectname, new.original_date, new.upgrade_date);

CREATE RULE update_age_info AS ON UPDATE
TO elementdata.age_information
DO INSTEAD UPDATE elementdata.data_age_information SET
projectname = new.projectname, original_date = new.original_date,
upgrade_date = new.upgrade_date WHERE (data_age_information.elementid =
old.elementid);

In my program, I should be able to select a room, click the <Attach> button,
and then select what table or view from the db I want to attach to the room
from a pop-up and view the info... But for this specific example
(Age_information VIEW), as soon as I select it from the pop-up box to look
at the info from the db, I get an error: "Cannot insert into a view." I
haven't even viewed it, much less attempted to insert into it; and let's say
I could get into it, is there anything wrong with my ON INSERT RULE that
would throw this error? Because on several of my other views, I can get
into them, but not insert into them when it appears that my ON INSERT
statements are correct.

Thanks so much for your time, help, and insight!!
-Jeanna

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2007-01-10 21:43:46 Re: Export to shape file
Previous Message chwy_nougat 2007-01-10 21:00:52 Re: remove embedded carriage returns