Re: Problems With VIEWS

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