Re: Support for %TYPE in CREATE FUNCTION

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I think the major problem was that our pg_proc table doesn't have any
> > way of handling arg changes. In fact, we need a ALTER FUNCTION
> > capability first so we can recreate functions in place with the same
> > OID.
>
> Actually that's the least of the issues. The real problem is that
> because of function overloading, myfunc(int4) and myfunc(int2) (for
> example) are considered completely different functions. It is thus
> not at all clear what should happen if I create myfunc(foo.f1%TYPE)
> and later alter the type of foo.f1 from int4 to int2. Does myfunc(int4)
> stop existing? What if a conflicting myfunc(int2) already exists?
> What happens to type-specific references to myfunc(int4) --- for
> example, what if it's used as the implementation function for an
> operator declared on int4?
>
> Worrying about implementation issues is premature when you haven't
> got an adequate definition.

It's pretty easy to define what to do in each of the cases you
describe. The options are: 1) leave the function unchanged; 2) alter
the function to use the new type; 3) define a copy of the function
with the new type. In cases 2 or 3 you have to consider whether there
is already a function with the new type; if there is, you have to
either: 23a) replace the new function; 23b) issue a NOTICE; 23c) issue
a NOTICE and drop the old function. In case 2 you also have to
consider whether something is using the old function; if there is, you
have to 2a) leave the old function there; 2b) issue a NOTICE while
dropping the old function.

I propose this: if a table definition changes, alter the function to
use the new type (choice 2). If there is already a function with the
new type, issue a NOTICE and drop the old function (choice 23b). If
something is using the old function, issue a NOTICE while dropping the
old function (choice 2b).

Of course, this is made much easier if there is a pg_depends table
which accurately records dependencies.

I have a meta-point: the choices to be made here are not all that
interesting. They do have to be defined. But almost any definition
is OK. Users are not going to routinely redefine tables with attached
functions; when they do, they must be prepared to consider the
consequences. If anybody thinks that different choices should be made
in this case, that is certainly fine with me.

If you agree with me on the meta-point, then this is just a quibble
about my original patch (which made choice 1 above). If you disagree
with me, I'd like to understand why.

Ian