Attached is a basic patch implementing create or replace trigger. It
passes all regression tests.

Gavin
Sun, 11 Aug 2002 19:06:50 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
CREATE OR REPLACE TRIGGER
Gavin Sherry <swm@linuxworld.com.au> writes:
> Attached is a basic patch implementing create or replace trigger.

Why would we need that, given that we now have CREATE OR REPLACE
FUNCTION?  It's not like anything could depend on a trigger
(other than FK constraints, and I don't think I want people replacing
trigger definitions that are part of an FK constraint...)

			regards, tom lane

Sun, 11 Aug 2002 11:01:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE OR REPLACE TRIGGER 
On Sun, 11 Aug 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > Attached is a basic patch implementing create or replace trigger.
> 
> Why would we need that, given that we now have CREATE OR REPLACE
> FUNCTION?  It's not like anything could depend on a trigger
> (other than FK constraints, and I don't think I want people replacing
> trigger definitions that are part of an FK constraint...)

I thought it conceivable that one may want to point the trigger at a
different function, change BEFORE or AFTER, or change the event(s) upon
which the trigger fires.

Gavin


Mon, 12 Aug 2002 01:09:58 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: CREATE OR REPLACE TRIGGER 
Gavin Sherry <swm@linuxworld.com.au> writes:
> On Sun, 11 Aug 2002, Tom Lane wrote:
>> Why would we need that, given that we now have CREATE OR REPLACE
>> FUNCTION?  It's not like anything could depend on a trigger
>> (other than FK constraints, and I don't think I want people replacing
>> trigger definitions that are part of an FK constraint...)

> I thought it conceivable that one may want to point the trigger at a
> different function, change BEFORE or AFTER, or change the event(s) upon
> which the trigger fires.

But there is no reason you can't just drop and recreate the trigger
to do those things.

Given the lack of popular demand (I don't think I've yet heard anyone
ask for this feature), I can't get excited about expending code bulk
and complexity on it --- the patch seems to make CreateTrigger
significantly messier, and that will cost us in maintenance effort
down the road.

			regards, tom lane

Sun, 11 Aug 2002 11:27:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE OR REPLACE TRIGGER 
On Sun, 11 Aug 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > On Sun, 11 Aug 2002, Tom Lane wrote:
> >> Why would we need that, given that we now have CREATE OR REPLACE
> >> FUNCTION?  It's not like anything could depend on a trigger
> >> (other than FK constraints, and I don't think I want people replacing
> >> trigger definitions that are part of an FK constraint...)
> 
> > I thought it conceivable that one may want to point the trigger at a
> > different function, change BEFORE or AFTER, or change the event(s) upon
> > which the trigger fires.
> 


> Given the lack of popular demand (I don't think I've yet heard anyone
> ask for this feature), I can't get excited about expending code bulk
> and complexity on it --- the patch seems to make CreateTrigger
> significantly messier, and that will cost us in maintenance effort
> down the road.

 From memory, I think some of the pgadmin guys jumped up and down about it
for a while: http://fts.postgresql.org/db/mw/msg.html?mid=1353289.

Gavin



Mon, 12 Aug 2002 01:39:14 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: CREATE OR REPLACE TRIGGER 
Gavin Sherry <swm@linuxworld.com.au> writes:
> From memory, I think some of the pgadmin guys jumped up and down about it
> for a while: http://fts.postgresql.org/db/mw/msg.html?mid=1353289.

Hmm.  I remember Poure asking repeatedly for CREATE OR REPLACE VIEW,
and that makes a lot of sense to me, because other things *can* depend
on a view.  (Unfortunately, by the same token it's a lot harder to do.)
The use-case for replacing a trigger is not visible to the naked eye.

			regards, tom lane

Sun, 11 Aug 2002 11:53:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE OR REPLACE TRIGGER 
On Sun, 11 Aug 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > From memory, I think some of the pgadmin guys jumped up and down about it
> > for a while: http://fts.postgresql.org/db/mw/msg.html?mid=1353289.
> 
> Hmm.  I remember Poure asking repeatedly for CREATE OR REPLACE VIEW,
> and that makes a lot of sense to me, because other things *can* depend
> on a view.  (Unfortunately, by the same token it's a lot harder to do.)

I know. I am finishing off the patch now.

> The use-case for replacing a trigger is not visible to the naked eye.

That's okay then. I should have thought about it harder.

Gavin


Mon, 12 Aug 2002 02:00:55 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: CREATE OR REPLACE TRIGGER 
Le Dimanche 11 Août 2002 17:53, Tom Lane a écrit :
> Hmm.  I remember Poure asking repeatedly for CREATE OR REPLACE VIEW,
> and that makes a lot of sense to me, because other things *can* depend
> on a view.  (Unfortunately, by the same token it's a lot harder to do.)
> The use-case for replacing a trigger is not visible to the naked eye.

Dear Tom,

Replacing a trigger is interesting, for several reasons:
- you may need to temporary disable a trigger. In pgAdmin2, you may move the 
view to a temporary table. This can be done by other means, but none is 
standard. By the way, a DISSABLE TRIGGER would be usefull.
- you may need to choose another function or change events.
- systems with server-side code need to be UPGRADED. Look at Compiere for 
example. When the database schema evolves, a scripts shall be able to run 
server-side and upgrade the database safely.
- newbees like to play around just as if they were in Access, Excel or MySQL. 
In pgAdmin2, the graphical presentation enables them to create, move, alter 
and delete objects. This is very important for someone who learns databases. 
Learning becomes a game.

Inside PostgreSQL backend, I see no reason why this should not be done by a 
DROP/CREATE.

Last of all, if all objects could be REPLACED or ALTERED inside PostgreSQL, it 
would become interesting to create automatic Diff between revisions of a 
schema. Then, PostgreSQL itself sould be able to write the upgrade script. 
What do you think of this advanced feature?

Do you think it is possible to store schema dumps inside postgreSQL and 
generate upgrade scripts between revisions?

Cheers,
Jean-Michel

Tue, 13 Aug 2002 12:10:12 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: CREATE OR REPLACE TRIGGER