Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3

Dear all,

I would like to know if "Add OR REPLACE" to-do item is on PostgreSQL 7.3
radar list.

There is a strong need for :
- CREATE OR REPLACE VIEW,
- CREATE OR REPLACE TRIGGER.

Please let me know if someone is working on theses features. This would help
pgAdmin II and other GUIs considerably.

Otherwise I will have to learn more C and do it myself (which I am not sure
to do because I am not a good programmer and do not know PostgreSQL
internals).

Thanks again to all of you,
Best regards,
Jean-Michel POURE

> - CREATE OR REPLACE VIEW,
> - CREATE OR REPLACE TRIGGER.

Isn't it the same as

DROP VIEW name
CREATE VIEW name
or
DROP TRIGGER name
CREATE TRIGGER name
??

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2501
Howitzvej 75 Åben 14.00-18.00 Web: www.suse.dk
2000 Frederiksberg Lørdag 11.00-17.00 Email: kar(at)kakidata(dot)dk

On Wed, 23 Jan 2002, Jean-Michel POURE wrote:

> Dear all,
>
> I would like to know if "Add OR REPLACE" to-do item is on PostgreSQL 7.3
> radar list.
>
> There is a strong need for :
> - CREATE OR REPLACE VIEW,
> - CREATE OR REPLACE TRIGGER.

I intend to do this.

Gavin

On Wed, 23 Jan 2002, Kaare Rasmussen wrote:

> > - CREATE OR REPLACE VIEW,
> > - CREATE OR REPLACE TRIGGER.
>
> Isn't it the same as
>
> DROP VIEW name
> CREATE VIEW name
> or
> DROP TRIGGER name
> CREATE TRIGGER name
> ??

The original OID is preserved.

Gavin

On Wed, Jan 23, 2002 at 02:44:17PM +0100, Jean-Michel POURE wrote:
> Otherwise I will have to learn more C and do it myself (which I am not sure
> to do because I am not a good programmer and do not know PostgreSQL
> internals).

It should be not very difficult. I think, the patch which implemented
CREATE OR REPLACE FUNCTION should tell you almost all the stuff.

It was checked-in primarily at 2001/10/02 21:39:35. So something like

$ cvs diff -D "2001-10-02 21:00" -D "2001-10-03"

in `src/include' and `src/backend' sub-directories should do a great job for
you. (It really does !)

--
Holger Krug
hkrug(at)rationalizer(dot)com

Holger Krug <hkrug(at)rationalizer(dot)com> writes:
> On Wed, Jan 23, 2002 at 02:44:17PM +0100, Jean-Michel POURE wrote:
>> Otherwise I will have to learn more C and do it myself (which I am not sure
>> to do because I am not a good programmer and do not know PostgreSQL
>> internals).

> It should be not very difficult. I think, the patch which implemented
> CREATE OR REPLACE FUNCTION should tell you almost all the stuff.

There was, however, quite a bit of thrashing-about later to make it
actually work reasonably well :-(

regards, tom lane

Le Mercredi 23 Janvier 2002 15:16, Gavin Sherry a écrit :
> > There is a strong need for :
> > - CREATE OR REPLACE VIEW,
> > - CREATE OR REPLACE TRIGGER.
> I intend to do this.
> Gavin

Gavin, you suddenly became the community hero. Can you hear the screems of
your fans? There is a crowd waiting for you. Where can I get a t-shirt with
your name on it?

With such features and the help of pgAdmin II, we can reach a new public of
end-users. When do you think this can be done?

Thanks again.

Best regards,
Jean-Michel POURE

Jean-Michel POURE wrote:
> Le Mercredi 23 Janvier 2002 15:16, Gavin Sherry a écrit :
> > > There is a strong need for :
> > > - CREATE OR REPLACE VIEW,
> > > - CREATE OR REPLACE TRIGGER.
> > I intend to do this.
> > Gavin
>
> Gavin, you suddenly became the community hero. Can you hear the screems of
> your fans? There is a crowd waiting for you. Where can I get a t-shirt with
> your name on it?

But please don't forget that simply overwriting an existing
pg_proc tuple will not cause a persistent database connection
to use the new version. PL/Tcl and PL/pgSQL for example cache
the precompiled procedure based on it's oid. So at least
think of a version count that get's bumped and checked in the
PL handlers.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Wed, 23 Jan 2002, Jean-Michel POURE wrote:

> Le Mercredi 23 Janvier 2002 15:16, Gavin Sherry a écrit :
> > > There is a strong need for :
> > > - CREATE OR REPLACE VIEW,
> > > - CREATE OR REPLACE TRIGGER.
> > I intend to do this.
> > Gavin
>

> With such features and the help of pgAdmin II, we can reach a new public of
> end-users. When do you think this can be done?

It depends when the patch would be put into a release. Its a small patch
but I am busy at the moment. I will probably do it in Feb.

Gavin

> Isn't it the same as
>
> DROP VIEW name
> CREATE VIEW name
> or
> DROP TRIGGER name
> CREATE TRIGGER name

Two examples :
1) Views with triggers
If you drop a view which has triggers and then re-recreate the view, the
triggers are lost.

2) Server side code consistency
Let's say you are working on a large view in pgAdmin II. If you drop a view
and recreate it, you can never be sure that the new version of the view will
ba accepted by PostgeSQL.

i.e. if the view definition is not correct, you loose your work.

Le Mercredi 23 Janvier 2002 15:09, Kaare Rasmussen a écrit :
> Isn't it the same as
> DROP VIEW name
> CREATE VIEW name
> DROP TRIGGER name
> CREATE TRIGGER name

No, CREATE OR REPLACE keeps oids, which is much more interesting.

A few examples :

1) Views with triggers.
Triggers can be used to update views. But in the case of a simple
DROP/CREATE, after dropping the view, the triggers are lost (depency problem).

IFirthermore, if we had CREATE OR REPLACE, we could have pgAdmin II create
the underlying triggers automatically to update views...

2) Server side code consistency
IMHO, it is not possible to DROP / CREATE a view in a single transaction.
Therefore, if you drop a view and the definition of the new view is
incorrect, you loose the view. You can always save the old view definition,
but this is not serious programming...

3) Future database IDE will be built upon abstraction layers (PEAR DB,
MetatData, GnomeDB) to enable easy migration from one database to another
(and make PostgreSQL world champion which is our goal). Abstraction layers
will not take into account things like DROP/CREATE.

In more general aspects :

At pgAdmin II, we would like to bundle pgAdmin with a Windows PostgreSQL
installer wizard. And upload the package to free downloading sites. We need
these features to say : "Look, PostgreSQL is the new AccessXP competitor" or
whatever. This will bring a new audience to PostgreSQL.

Best regards,
Jean-Michel POURE

On Thu, Jan 24, 2002 at 05:21:14PM +0100, Jean-Michel POURE wrote:
> Le Mercredi 23 Janvier 2002 15:09, Kaare Rasmussen a ?crit :
> > Isn't it the same as
> > DROP VIEW name
> > CREATE VIEW name
> > DROP TRIGGER name
> > CREATE TRIGGER name
>
> No, CREATE OR REPLACE keeps oids, which is much more interesting.
>
> A few examples :
>
> 1) Views with triggers.
> Triggers can be used to update views. But in the case of a simple
> DROP/CREATE, after dropping the view, the triggers are lost (depency problem).
>
> IFirthermore, if we had CREATE OR REPLACE, we could have pgAdmin II create
> the underlying triggers automatically to update views...

Just a nomenclature/language thing: a trigger on a view won't do very
much, and I think can not be created in 7.2. A trigger fires when
tuples are about to be stored or accessed from storage: since a view
_has_ no storage of it's own (well, none that's used) a trigger will
never fire. Rules, on the other hand, rewrite the SQL: that's how views
are implemented. So, you mean ' VIEWs with additional rules'.

Ross

Le Jeudi 24 Janvier 2002 17:58, vous avez écrit :
> Rules, on the other hand, rewrite the SQL: that's how views
> are implemented.  So, you mean ' VIEWs with additional rules'.

You are right. Thank you.
Jean-Michel POURE