Problems With VIEWS

Поиск
Список
Период
Сортировка
От Jeanna Geier
Тема Problems With VIEWS
Дата
Msg-id FBEGJLLJBCOMCDBJHIMECENJCCAA.jgeier@apt-cafm.com
обсуждение исходный текст
Ответы Re: Problems With VIEWS  (Richard Broersma Jr <rabroersma@yahoo.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: brian
Дата:
Сообщение: Re: Trying to load MySQL data
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: Export to shape file