Re: Function and Procedure with same signature? - Mailing list pgsql-hackers

On Thu, Mar 7, 2024 at 5:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Hannu Krosing <hannuk@google.com> writes:
> > On Sat, Feb 10, 2024 at 12:38 AM Tom Lane <tgl@sss.pgh.pa.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
...

Thanks for thorough analysis of the standard.

I went and looked at more what other relevant database do in this
space based on their documentation

Tl;DR

* MS SQL Server
   - no overloading allowed anywhere
* MySQL
   - no overloading
* Oracle
   - no overloading at top level
   - overloading and independent namespaces for functions and procedures
* Teradata
   - function overloading allowed
   - not clear from documentation if this also applies procedures
   - function overloading docs does not mention possible clashes with
procedure names anywhere
* DB2
   - function overloading fully supported
   - procedure overloading supported, but only for distinct NUMBER OF ARGUMENTS

I'll try to get access to a Teradata instance to verify the above

So at high level most other Serious Databases
  - do support function overloading
  - keep functions and procedures in separated namespaces

I still think that PostgreSQL having functions and procedures share
the same namespace is an unneeded and unjustified restriction


I plan to do some hands-on testing on Teradata and DB2 to understand it

But my current thinking is that we should not be more restrictive than
others unless there is a strong technical reason for it. And currently
I do not see any.

> 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.

The main issue is not dropping them, but inability to determine which
one to call.

We already have this in case of two overloaded functions with same
initial argument types and the rest having defaults - when

---
hannuk=# create function get(i int, out o int) begin atomic select i; end;
CREATE FUNCTION
hannuk=# create function get(i int, j int default 0, out o int) begin
atomic select i+j; end;
CREATE FUNCTION
hannuk=# select get(1);
ERROR:  function get(integer) is not unique
LINE 1: select get(1);
               ^
HINT:  Could not choose a best candidate function. You might need to
add explicit type casts.
---

> 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 again has a good reason, as there would be many cases where you
could not decide which one to call

Not allowing overloading based on only return types is common across
all OO languages.

My point is that this does not apply to FUNCTION vs. PROCEDURE as it
is very clear from the CALL syntax which one is meant.

> 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 think ROUTINE being unambiguous is not e very good goal.

What if next version of standard introduces DROP DATABASE OBJECT ?

> 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.

Fully agree that  "Oracle allows it" is a non-argument.

My main point is that there is no strong reason to have objects which
are distinct at syntax level to be in the same namespace.

# Oracle is actually much more restrictive in top level object namespace -

All of the following share the same namespace - [Packages, Private
synonyms, Sequences, Stand-alone procedures, Stand-alone stored
functions, Tables, User-defined operators, User-defined types, Views].
(I guess this makes parser easier to write)

The equivalent in postgreSQL would be [extensions, schemas, tables,
procedures, functions and a few more] all sharing the namespace.

Where Oracle *does* allow overloading is "packaged functions and
procedures" which are probably using a separate parser altogether.

My (wildly speculative) explanation of the above is that when creating
the top-level syntax requirements it was easiest to just not allow any
need to complex determination as the aim was to get the whole thing
out quickly.

Later, when adding the package support there was more time to pay
attention to eas-of-use for developers, so overloading and
non-name-basesd distinction between objects, including functions and
procedures was added.

# DB2- I also checked wher DB2 does and it has a different set of rules

1. FUNCTIONS and METHODS share a namespace in a way that when
overloading the "type instance" of a method is moved to first argument
of function and then the uniqueness of argument list is checked.

2. PROCEDURES can be overloaded but you can not have two procedures
with same NUMBER OF ARGUMENTS irrespective of types

but there is no mention anywhere about signatures having to be
different between FUNCTIONS and PROCEDURES.