Skip site navigation (1) Skip section navigation (2)

Re: CREATE OR REPLACE FUNCTION

From: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: CREATE OR REPLACE FUNCTION
Date: 2001-09-22 11:55:13
Message-ID: Pine.LNX.4.21.0109221538400.23279-200000@linuxworld.com.au (view raw or flat)
Thread:
Lists: pgsql-patches
On Thu, 20 Sep 2001, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> > Here's a patch I put together. I thought the Oracle-style 'CREATE OR
> > REPLACE FUNCTION' syntax might be useful to those doing lots of function
> > creation/development. It is against the 7.1.3 source.
> 
> Hmm.  There are a couple of things that are a tad ugly about this patch
> --- you should be heap_update'ing the pg_proc entry, not doing a delete

Oops. Attached patch uses heap_update. I wasn't sure if I should be
getting it from SearchSysCache() or with a direct heap scan. I used the
first.

> and insert --- but the main thing I don't like is that there's no
> checking to ensure that the function return type doesn't change.  We
> can't allow that; it'd break stored views etc that use the function.

This is in the attached patch. 

On this, the problem is that if a function is updated and has a different
return type and the function is used by triggers, views etc then these
mechanisms will be immediately corrupted. The same is true if a function
whose return type remains the same generates incorrect data. Naturally
this is the user's problem. But would it be useful to have a refcount in
pg_proc which gets updated when triggers, views, etc are
created/removed? If refcount == 0, the function can be updated (even with
different return type), otherwise the user has to go to DROP FUNCTION.

Seems like a fair bit of trouble involved. But may be useful.

> 
> It'd probably also be a good idea to insist that the replacer be the
> same as the original owner.  (Possibly RemoveFunction does that for you
> in the patch as it stands, but it'll need an explicit test if you go
> the update route.)

RemoveFunction() does that. I have included a check for this in the
attached patch.

> 
> BTW, I've been assuming that when we got around to providing a
> capability like this, it'd be via an "ALTER FUNCTION" kind of
> statement.  Does anyone have a strong feeling pro or con on whether
> "CREATE OR REPLACE" is a preferable approach?  It doesn't seem to
> fit with the spirit of our other maintenance commands, but maybe
> we should just bow down before the Oracle and do it their way...

I prefer the CREATE OR REPLACE syntax. It makes sense to want to replace a
function even if you don't know if it exists.

AS for bowing down to Oracle, I'm sure oracle users moving to Postgres
would have no problem with syntax similiarities ;-)

Either way, its a simple change to move it to an ALTER FUNCTION syntax.

Gavin

Attachment: rf4.patch
Description: text/plain (9.9 KB)

In response to

pgsql-patches by date

Next:From: Gavin SherryDate: 2001-09-22 11:59:40
Subject: Re: CREATE OR REPLACE FUNCTION
Previous:From: Bruce MomjianDate: 2001-09-22 04:52:35
Subject: Re: pgcrypto update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group