Re: Function and Procedure with same signature?

Hannu Krosing <hannuk(at)google(dot)com> writes:
> On Sat, Feb 10, 2024 at 12:38 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Worth noting perhaps that this is actually required by the SQL
>> standard: per spec, functions and procedures are both "routines"
>> and share the same namespace,

> Can you point me to a place in the standard where it requires all
> kinds of ROUTINES to be using the same namespace ?

[ digs around a bit... ] Well, the spec is vaguer about this than
I thought. It is clear on one thing: 11.60 <SQL-invoked routine>
conformance rules include

2) Without Feature T341, “Overloading of SQL-invoked functions and
SQL-invoked procedures”, conforming SQL language shall not
contain a <schema routine> in which the schema identified by the
explicit or implicit <schema name> of the <schema qualified
routine name> includes a routine descriptor whose routine name is
<schema qualified routine name>.

("<schema routine>" means a CREATE FUNCTION or CREATE PROCEDURE
statement.)

That is, basic SQL doesn't allow you to have two routines with the
same qualified name at all, whether they have different types and
parameter lists or not. Now the above text is the entire definition
of T341, and it doesn't say just what an implementation that claims
feature T341 is expected to allow. Looking through the rest of 11.60,
we find

9) u) The schema identified by the explicit or implicit <schema
name> of the <specific name> shall not include a routine
descriptor whose specific name is equivalent to <specific name> or
a user-defined type descriptor that includes a method
specification descriptor whose specific name is equivalent to
<specific name>.

which evidently is describing the base case (with no mention of T341).
We also find

20) Case:

a) If R is an SQL-invoked procedure, then S shall not include
another SQL-invoked procedure whose <schema qualified routine
name> is equivalent to RN and whose number of SQL parameters is
PN.

which is a weird halfway measure indeed, and there's no
acknowledgement that this contradicts 9u. The other arm of the case
is pretty impenetrable prose, but what it appears to be saying is that
you can't create two functions of the same name and same number of
parameters if that would create any call-time ambiguity, that is that
a call could be written that might refer to either. So I guess these
paras are meant to explain what should happen if T341 is implemented,
but it's far from clear, and certainly their restrictions on
overloading are much stronger than what we allow.

If you look in 10.6 <specific routine designator> (which is what is
referenced by 11.62 <drop routine statement>) you find

4) If <routine type> specifies ROUTINE, then there shall be
exactly one SQL-invoked routine in the schema identified by SCN
whose <schema qualified routine name> is RN such that, for all i,
1 (one) ≤ i ≤ the number of arguments, when the Syntax Rules of
Subclause 9.25, “Data type identity”, are applied with the
declared type of its i-th SQL parameter as TYPE1 and the i-th
<data type> in the <data type list> of MN as TYPE2, those Syntax
Rules are satisfied. The <specific routine designator> identifies
that SQL-invoked routine.

It could be argued that this doesn't prohibit having both a function
and a procedure with the same data type list, only that you can't
write ROUTINE when trying to drop or alter one. But I think that's
just motivated reasoning. The paragraphs for <routine type> being
FUNCTION or PROCEDURE are exactly like the above except they say
"exactly one function" or "exactly one procedure". If you argue that
this text means we must allow functions and procedures with the same
parameter lists, then you are also arguing that we must allow multiple
functions with the same parameter lists, and it's just the user's
tough luck if they need to drop one of them.

A related point is that our tolerance for overloading routine
names isn't unlimited: we don't allow duplicate names with the
same list of input parameters, even if the output parameters are
different. This is not something that the SQL spec cares to
address, but there are good ambiguity-avoidance reasons for it.
I think limiting overloading so that a ROUTINE specification is
unambiguous is also a good thing.

I remain unexcited about changing our definition of this.
"Oracle allows it" is not something that has any weight in
my view: they have made a bunch of bad design decisions
as well as good ones, and I think this is a bad one.

regards, tom lane