On Tue, 23 Oct 2001, Bruce Momjian wrote:

> > Dear all,
> >
> > Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in
> > PostgreSQL 7.2?

Probably not, it's rather late in the cycle (isn't beta imminent?). Oh,
I'd vote for "OR REPLACE" as there's already an opt_or_replace
non-terminal in the parser. Adding an optional "OR DROP" might displease
yacc, and also follows in the same vein as what we have for CREATE
FUNCTION.

> > 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.

Take care,

Bill


Mon, 22 Oct 2001 11:45:04 -0700 (PDT)
Bill Studenmund <wrstuden@netbsd.org>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001, Bruce Momjian wrote:

> > 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?

Sounds good to me. :-)

Take care,

Bill


Tue, 23 Oct 2001 03:34:29 -0700 (PDT)
Bill Studenmund <wrstuden@netbsd.org>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
Dear all,

Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
PostgreSQL 7.2?

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).

We are also waiting for a proper ALTER table DROP column but we are day 
dreamers...

Thanks for your help and comprehension.
Best regards,
Jean-Michel POURE
pgAdmin team

Tue, 23 Oct 2001 17:16:06 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
CREATE OR REPLACE VIEW/TRIGGER
Jean-Michel POURE <jm.poure@freesurf.fr> writes:
> Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
> PostgreSQL 7.2?

We're already vastly overdue for beta.  The time for new feature
requests for 7.2 is past ... especially nontrivial requests.

			regards, tom lane

Tue, 23 Oct 2001 14:29:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER 
> We are also waiting for a proper ALTER table DROP column but we are day 
> dreamers...

This is a good example of bad management on our parts.  We couldn't
decide between two possible DROP COLUMN implementations, so we now have
the worst result, which is no implementation at all.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Oct 2001 19:19:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
> Dear all,
> 
> Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
> PostgreSQL 7.2?
> 
> 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?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Oct 2001 19:22:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
> > > 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?


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Oct 2001 20:56:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

I have added this to the TODO list:

	* Add OR REPLACE clauses to non-FUNCTION object creation

I think there are clearly some other objects that need OR REPLACE.  Not
sure which ones yet.


---------------------------------------------------------------------------

> Dear all,
> 
> Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
> PostgreSQL 7.2?
> 
> 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).
> 
> We are also waiting for a proper ALTER table DROP column but we are day 
> dreamers...
> 
> Thanks for your help and comprehension.
> Best regards,
> Jean-Michel POURE
> pgAdmin team
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Oct 2001 21:01:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CREATE OR REPLACE VIEW/TRIGGER
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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?

Not until we do the necessary legwork.  I spent a good deal of time over
the past week making the various PL modules react to replacement of
pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter
a week or so back).  CREATE OR REPLACE VIEW implies updating cached
query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies.
But I am pretty sure it's not a trivial question.

In short: put it on the todo list, but note that there are some
implications...

			regards, tom lane

Wed, 24 Oct 2001 00:53:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER 
> Not until we do the necessary legwork.  I spent a good deal of time over
> the past week making the various PL modules react to replacement of
> pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter
> a week or so back).  CREATE OR REPLACE VIEW implies updating cached
> query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies.
> But I am pretty sure it's not a trivial question.
> 
> In short: put it on the todo list, but note that there are some
> implications...

That's all I needed to know.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 24 Oct 2001 00:55:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] 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

Wed, 24 Oct 2001 01:13:43 -0400
Mike Mascari <mascarm@mascari.com>
Re: CREATE OR REPLACE VIEW/TRIGGER
Bill Studenmund writes:

> > 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.

Technically, it's not an error, it's an "exception condition".  This might
make you feel better when consciously ignoring it. ;-)

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 24 Oct 2001 23:56:01 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001 17:16:06 +0200, you wrote:
>CREATE OR DROP VIEW 

Is this for real? If I were a database server I would say to the
client "please make up your mind" :-)

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sat, 27 Oct 2001 13:50:28 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

> >CREATE OR DROP VIEW
>Is this for real? If I were a database server I would say to the
>client "please make up your mind" :-)

I meant DROP IF EXISTS and then CREATE.
This is more simple to implement than CREATE OR REPLACE.

Best regards,
Jean-Michel POURE

Sat, 27 Oct 2001 15:50:11 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: CREATE OR REPLACE VIEW/TRIGGER