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