Thread: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
Jean-Michel POURE
Date:
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
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
"Kaare Rasmussen"
Date:
> - 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@kakidata.dk
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, 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, 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@rationalizer.com
Holger Krug <hkrug@rationalizer.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
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
Jean-Michel POURE
Date:
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@Yahoo.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
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
Jean-Michel POURE
Date:
> 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.
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
Jean-Michel POURE
Date:
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
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
"Ross J. Reedstrom"
Date:
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
Re: Add OR REPLACE clauses to non-FUNCTION object creation in PostgreSQL 7.3
From
Jean-Michel POURE
Date:
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