Re: CREATE OR REPLACE VIEW/TRIGGER

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