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

Hi Peter and Tom

Following up on our conversation art pgcon.dev

If I understood correctly Peter has some old patch for splitting the
namespaces which could be resurrected to try to move forward on this ?

Can you share what you did there ?

Also, while at it we should extend the function lookup to support full
"method call syntax" in general, up from one-argument case  so that

SELECT function(a_thing, arg2, arg 2, ...)

could also be called as

SELECT a_thing.function(arg2, arg 2, ...)


--
Hannu



On Mon, Mar 11, 2024 at 12:55 PM Hannu Krosing <hannuk@google.com> wrote:
>
> 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.