Column DEFAULT values aren't automatically "inherited" from the base relation. This means somebody has to do the following, to get view updates with related sequences succeed:
=> Issue an ALTER TABLE view ALTER COLUMN col1 SET DEFAULT nextval('...') to get an DEFAULT value from a sequence for example. Is it a good idea to move this into the view update rule code or into the CREATE VIEW command? Is the possibility to ALTER a view bulletproof or only a side-effect with the future to be broken someday (because it looks not very intuitive...)? Here's a short example, what i mean:
[EMAIL PROTECTED]:yomama #= CREATE OR REPLACE VIEW vabteilung_edv AS SELECT bezeichnung AS c2, abteilungsnummer AS c1 from abteilung where bezeichnung LIKE 'EDV%' WITH LOCAL CHECK OPTION;
NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules
CREATE VIEW
[EMAIL PROTECTED]:yomama #= \d abteilung
Table "public.abteilung"
Column | Type | Modifiers
------------------+---------+-------------------------------------------------------------------------
abteilungsnummer | integer | not null default nextval('public.abteilung_abteilungsnummer_seq'::text)
bezeichnung | text | not null
Indexes:
"abteilung_pkey" PRIMARY KEY, btree (abteilungsnummer)
[EMAIL PROTECTED]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'edv abteilung' );
ERROR: view update commands violates rule condition
[EMAIL PROTECTED]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'EDV abteilung' );
ERROR: null value in column "abteilungsnummer" violates not-null constraint
==> the error message is confusing.....
[EMAIL PROTECTED]:yomama #= ALTER TABLE vabteilung_edv ALTER COLUMN c1 SET DEFAULT nextval('public.abteilung_abteilungsnummer_seq'::text);
ALTER TABLE
[EMAIL PROTECTED]:yomama #= INSERT INTO vabteilung_edv( c2 ) VALUES ( 'EDV abteilung' );
INSERT 107905 1
[EMAIL PROTECTED]:yomama #= select * from vabteilung_edv;
c2 | c1
---------------+----
EDV abteilung | 6
(1 row)Implicit created rules are named _INSERT, _DELETE and _UPDATE and so no other rules are allowed to live besides them to get the view update code working. However, this breaks pg_dump restore's likely, but there is another issue:
=> Views without the CHECK OPTION are intended to be updated in any manner you can imagine. So, if a view only displays ID's > 5, you are allowed to update ID's <= 5 according to the SQL92 Standard. With rules only, this is not possible, since the planner doesn't see the affected tuples through the view. What should be done in this case? One possibility is to hack the planner/rewriter to get the specific tuples visible, but i don't think this is a good idea. The other possibility is to consider views without the CHECK OPTION read-only, which would help to not break any database dump's with views, which have user defined update rules.
Any comments, thoughts or opinions?
--
Bernd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
