Bruce Momjian wrote:
>
> > > > Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These
> > > > features are needed for pgAdmin II (we could also provide a patch for
> > > > PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for
> > > > pseudo-modification solutions (which is definitely not a good solution).
> > >
> > > Our current CREATE OR REPLACE FUNCTION perserves the OID of the
> > > function. Is there similar functionality you need where a simple
> > > DROP (ignore the error), CREATE will not work?
> >
> > If possible, it's nice to not have commands whose error codes you ignore.
> > That way if you see an error, you know you need to do something about it.
>
> Folks, is this a valid reason for adding OR REPLACE to all CREATE object
> commands?
Well, Oracle has CREATE OR REPLACE for:
Views
Functions
Procedures
Triggers
Types
Packages
but not for (at least 8.0.5):
Tables
Indexes
Sequences
At first glance, I'm not sure why Oracle doesn't allow for the
replacement of the non-"compiled" objects. Perhaps the complexities
involved in enforcing RI was too much. The *major* advantage to
allowing a REPLACE in Oracle is to preserve permissions granted to
various users and groups (aka ROLES). Oracle automatically
recompiles views, functions, procedures, etc. if their underlying
dependencies change:
SQL> CREATE TABLE employees (key integer, salary float);
Table created.
SQL> CREATE VIEW salaries AS SELECT * FROM employees WHERE salary <
15000;
View created.
SQL> SELECT * FROM salaries;
no rows selected
SQL> DROP TABLE employees;
Table dropped.
SQL> SELECT * FROM salaries;
SELECT * FROM salaries *
ERROR at line 1:
ORA-04063: view "MASCARM.SALARIES" has errors
SQL> CREATE TABLE employees (key integer, salary float);
Table created.
SQL> SELECT * FROM salaries;
no rows selected
So it seems to me that the major reason is to preserve GRANT/REVOKE
privileges issues against the object in question.
FWIW,
Mike Mascari
mascarm@mascari.com