Thread: Problems With VIEWS
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
> 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. Turn on Statement logging in you postgresql.conf file. Try and insert and after it fails examine the insert statement and notice the error message. REgards, Richard Broersma Jr.
Thanks, Richard!
Doing so produced the following message in my log file:
2007-01-10 16:45:33 ERROR: cannot insert into a view
2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO INSTEAD
rule.
That's why I like this forum, people are so knowledgeable, helpful, and
quick to answer!
Now, just gotta research this some more and I'll post my solution once it's
working so that others who may search may find something helpful in one of
my posts as I have in so many others.
Thanks again!
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma
Jr
Sent: Wednesday, January 10, 2007 4:32 PM
To: Jeanna Geier; pgsql-general
Subject: Re: [GENERAL] Problems With VIEWS
> 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.
Turn on Statement logging in you postgresql.conf file. Try and insert and
after it fails examine
the insert statement and notice the error message.
REgards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
> 2007-01-10 16:45:33 ERROR: cannot insert into a view > 2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO INSTEAD > rule. Also, I see you have this field in your insert rule: new.upgrade_date but I see so upgrade_date in you view's definition. What exactly do you intend to be inserted. All of the New.[field_names] in the insert statement must be view fieldnames not table fieldnames. Regards, Richard Broersma Jr.
I guess I have a general question regarding Postgres and RULES -- relative newbie here, especially with these 'custom' functions -- where/how can you view the RULES in Postgres? I use CaseStudio to generate my Schemas/create my Rules and then import them into Postgres to create my tables and views - is there anyway for me to view them in Postgres to verify that they got imported correctly? I can view the .sql files that I imported to verify the CREATE functions, etc., but when I search these files, the "CREATE RULE" commands are not in there... Thanks much, -Jeanna -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma Jr Sent: Wednesday, January 10, 2007 5:04 PM To: Jeanna Geier; pgsql-general Subject: Re: [GENERAL] Problems With VIEWS > 2007-01-10 16:45:33 ERROR: cannot insert into a view > 2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO INSTEAD > rule. Also, I see you have this field in your insert rule: new.upgrade_date but I see so upgrade_date in you view's definition. What exactly do you intend to be inserted. All of the New.[field_names] in the insert statement must be view fieldnames not table fieldnames. Regards, Richard Broersma Jr. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Select * from pg_rules ?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeanna Geier
Sent: Wednesday, January 10, 2007 5:43 PM
To: Richard Broersma Jr; pgsql-general
Subject: Re: [GENERAL] Problems With VIEWS
I guess I have a general question regarding Postgres and RULES --
relative
newbie here, especially with these 'custom' functions -- where/how can
you
view the RULES in Postgres?
I use CaseStudio to generate my Schemas/create my Rules and then import
them
into Postgres to create my tables and views - is there anyway for me to
view
them in Postgres to verify that they got imported correctly? I can view
the
.sql files that I imported to verify the CREATE functions, etc., but
when I
search these files, the "CREATE RULE" commands are not in there...
Thanks much,
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma
Jr
Sent: Wednesday, January 10, 2007 5:04 PM
To: Jeanna Geier; pgsql-general
Subject: Re: [GENERAL] Problems With VIEWS
> 2007-01-10 16:45:33 ERROR: cannot insert into a view
> 2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO
INSTEAD
> rule.
Also, I see you have this field in your insert rule:
new.upgrade_date
but I see so upgrade_date in you view's definition. What exactly do you
intend to be inserted.
All of the New.[field_names] in the insert statement must be view
fieldnames
not table fieldnames.
Regards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
also, if you open up psql, you can type "\d <view_name>" and it will tell you everything about the view. --- Adam Rich <adam.r@sbcglobal.net> wrote: > > Select * from pg_rules ? > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeanna Geier > Sent: Wednesday, January 10, 2007 5:43 PM > To: Richard Broersma Jr; pgsql-general > Subject: Re: [GENERAL] Problems With VIEWS > > > I guess I have a general question regarding Postgres and RULES -- > relative > newbie here, especially with these 'custom' functions -- where/how can > you > view the RULES in Postgres? > > I use CaseStudio to generate my Schemas/create my Rules and then import > them > into Postgres to create my tables and views - is there anyway for me to > view > them in Postgres to verify that they got imported correctly? I can view > the > .sql files that I imported to verify the CREATE functions, etc., but > when I > search these files, the "CREATE RULE" commands are not in there... > > Thanks much, > -Jeanna > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma > Jr > Sent: Wednesday, January 10, 2007 5:04 PM > To: Jeanna Geier; pgsql-general > Subject: Re: [GENERAL] Problems With VIEWS > > > > 2007-01-10 16:45:33 ERROR: cannot insert into a view > > 2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO > INSTEAD > > rule. > > Also, I see you have this field in your insert rule: > > new.upgrade_date > > but I see so upgrade_date in you view's definition. What exactly do you > intend to be inserted. > All of the New.[field_names] in the insert statement must be view > fieldnames > not table fieldnames. > > Regards, > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > >
Once again, thanks for the help.
OK, so I did the Adam's suggestion: SELECT * FROM pg_rules
and got the following returned:
apt=# select * from pg_rules;
schemaname | tablename | rulename |
definition
------------+-------------+---------------+---------------------------------
----
----------------------------------------------------------------------------
----
--------------------------------
pg_catalog | pg_settings | pg_settings_u |
CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name =
old.name) DO SELECT set_config(old.name, new
.setting, false) AS set_config;
pg_catalog | pg_settings | pg_settings_n |
CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
(2 rows)
and then Richard's for the "age_information" VIEW specifically:
apt=# \d elementdata.age_information
View "elementdata.age_information"
Column | Type | Modifiers
-----------------+-------------------+-----------
elementid | bigint |
projectname | character varying |
original_date | date |
age_yrs | double precision |
upgrade_date | date |
upgrade_age_yrs | double precision |
View definition:
SELECT data_age_information.elementid, data_age_information.projectname,
data_a
ge_information.original_date, (date_part('year'::text,
age(data_age_information.
original_date::timestamp with time zone)) * 12::double precision +
date_part('mo
nth'::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.upgr
ade_date::timestamp with time zone))) / 12::double precision AS
upgrade_age_yrs
FROM elementdata.data_age_information;
Obviously, there are no RULES associated with this VIEW, or any other view
that I have created in my db (all of which have at least two, most
three -INSERT, DELETE, & UPDATE- RULES associated with them...)
So, herein probably lies my problem with the "Cannont insert into a view"
error I'm getting anytime I'm attempting to access it from my program -
there are no rules set up for them, right? So, if I manually enter them for
each VIEW and do a BACKUP and then RESTORE, should they still be attached to
each of the VIEWS? I would think CASEStudio would have this capabilty to
export these, but at this point I need to get our application and the VIEWS
working and if I need to take the time to enter each RULE for the VIEWS
manually, so be it.
Thoughts and feedback, as always, are very much welcomed!
Thanks again,
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma
Jr
Sent: Wednesday, January 10, 2007 6:14 PM
To: Adam Rich; 'Jeanna Geier'; 'pgsql-general'
Subject: Re: [GENERAL] Problems With VIEWS
also,
if you open up psql, you can type "\d <view_name>"
and it will tell you everything about the view.
--- Adam Rich <adam.r@sbcglobal.net> wrote:
>
> Select * from pg_rules ?
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeanna Geier
> Sent: Wednesday, January 10, 2007 5:43 PM
> To: Richard Broersma Jr; pgsql-general
> Subject: Re: [GENERAL] Problems With VIEWS
>
>
> I guess I have a general question regarding Postgres and RULES --
> relative
> newbie here, especially with these 'custom' functions -- where/how can
> you
> view the RULES in Postgres?
>
> I use CaseStudio to generate my Schemas/create my Rules and then import
> them
> into Postgres to create my tables and views - is there anyway for me to
> view
> them in Postgres to verify that they got imported correctly? I can view
> the
> .sql files that I imported to verify the CREATE functions, etc., but
> when I
> search these files, the "CREATE RULE" commands are not in there...
>
> Thanks much,
> -Jeanna
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Broersma
> Jr
> Sent: Wednesday, January 10, 2007 5:04 PM
> To: Jeanna Geier; pgsql-general
> Subject: Re: [GENERAL] Problems With VIEWS
>
>
> > 2007-01-10 16:45:33 ERROR: cannot insert into a view
> > 2007-01-10 16:45:33 HINT: You need an unconditional ON INSERT DO
> INSTEAD
> > rule.
>
> Also, I see you have this field in your insert rule:
>
> new.upgrade_date
>
> but I see so upgrade_date in you view's definition. What exactly do you
> intend to be inserted.
> All of the New.[field_names] in the insert statement must be view
> fieldnames
> not table fieldnames.
>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
> So, herein probably lies my problem with the "Cannont insert into a view" > error I'm getting anytime I'm attempting to access it from my program - > there are no rules set up for them, right? Correct, without insert rules you will not be able to add new records to the underlying tables of a view. > So, if I manually enter them for > each VIEW and do a BACKUP and then RESTORE, should they still be attached to > each of the VIEWS? What exactly would you be backing up and restoring? I expect that you can add new rules to these views from psql without even disconnecting from CASEStudio or anyother client program. Once the rulse are added, inserts that did not work before will start working. > I would think CASEStudio would have this capabilty to > export these, but at this point I need to get our application and the VIEWS > working and if I need to take the time to enter each RULE for the VIEWS > manually, so be it. Either way. I like to create sql files with all of the DDL for creating the view and rules. Overtime, if I need to change my view or reconfigure the rules, I can edit my sql file and then call it up in psql using "\e <view_def.sql>" I use the "CREATE OR REPLACE VIEW" syntax to achieve this. Regards, Richard Broersma Jr.
On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > Either way. I like to create sql files with all of the DDL for creating > the view and rules. > Overtime, if I need to change my view or reconfigure the rules, I can edit > my sql file and then > call it up in psql using "\e <view_def.sql>" > > I use the "CREATE OR REPLACE VIEW" syntax to achieve this. > Additional note: REPLACE doesn't work if you are going to change the list/type/name of your view columns. In 8.2 and above you could use DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. Bernd
Bernd Helmle wrote: > > On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > >> Either way. I like to create sql files with all of the DDL for creating >> the view and rules. >> Overtime, if I need to change my view or reconfigure the rules, I can edit >> my sql file and then >> call it up in psql using "\e <view_def.sql>" >> >> I use the "CREATE OR REPLACE VIEW" syntax to achieve this. >> >> > > Additional note: REPLACE doesn't work if you are going to change the > list/type/name of your view columns. In 8.2 and above you could use > DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. > Does anybody have a reason why this is the case. I can change all those things for a table without dropping it, why can't I do the same on a view? > Bernd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > >
Thanks, everyone - I really appreciate everyone's inupt and responses! Got this resolved and added the RULES to my .sql file. So far, so good. -Jeanna -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bernd Helmle Sent: Thursday, January 11, 2007 9:02 AM To: Richard Broersma Jr Cc: Jeanna Geier; Adam Rich; pgsql-general Subject: Re: [GENERAL] Problems With VIEWS On Thu, 11 Jan 2007 06:36:34 -0800 (PST), Richard Broersma Jr <rabroersma@yahoo.com> wrote: > > Either way. I like to create sql files with all of the DDL for creating > the view and rules. > Overtime, if I need to change my view or reconfigure the rules, I can edit > my sql file and then > call it up in psql using "\e <view_def.sql>" > > I use the "CREATE OR REPLACE VIEW" syntax to achieve this. > Additional note: REPLACE doesn't work if you are going to change the list/type/name of your view columns. In 8.2 and above you could use DROP VIEW IF EXISTS combined with a following CREATE VIEW, instead. Bernd ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend