Thread: Function and Procedure with same signature?
Hello Hackers,
Folks, When tried to create a function with the same signature as procedure it fails.
postgres=# create or replace procedure obj1(char) language plpgsql as $$ begin select $1; end; $$;
CREATE PROCEDURE
postgres=# create or replace function obj1(char) returns void language sql as $$ select $1 $$;
ERROR: cannot change routine kind
DETAIL: "obj1" is a procedure.
CREATE PROCEDURE
postgres=# create or replace function obj1(char) returns void language sql as $$ select $1 $$;
ERROR: cannot change routine kind
DETAIL: "obj1" is a procedure.
any reason for failures?
Can procedure or function can be defined with the same signature i.e. name and IN arguments ?
as callable for both is different.?
as callable for both is different.?
On Fri, Feb 9, 2024, 12:05 Deepak M <mahtodeepak05@gmail.com> wrote:
Hello Hackers,
Wrong list, this is for discussions regarding patches.
Folks, When tried to create a function with the same signature as procedure it fails.
That seems like a good hint you cannot do it. Specifically because they get defined in the same internal catalog within which names must be unique.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Feb 9, 2024, 12:05 Deepak M <mahtodeepak05@gmail.com> wrote:
>> Folks, When tried to create a function with the same signature as
>> procedure it fails.
> That seems like a good hint you cannot do it. Specifically because they
> get defined in the same internal catalog within which names must be unique.
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, which is necessary so that commands
like DROP ROUTINE are well-defined.
regards, tom lane
Hi Tom On Sat, Feb 10, 2024 at 12:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > On Fri, Feb 9, 2024, 12:05 Deepak M <mahtodeepak05@gmail.com> wrote: > >> Folks, When tried to create a function with the same signature as > >> procedure it fails. > > > That seems like a good hint you cannot do it. Specifically because they > > get defined in the same internal catalog within which names must be unique. The fact that we currently enforce it this way seems like an implementation deficiency that should be fixed. Bringing this up again, as there seems to be no good reason to have this restriction as there is no place in the call syntax where it would be unclear if a FUNCTION or a PROCEDURE is used. And at least Oracle does allow this (and possibly others) so having this unnecessary restriction in PostgreSQL makes migrations from Oracle applications where this feature is used needlessly complicated. > 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 ? All I could find was that ROUTINES are either FUNCTIONS, PROCEDURES or METHODS and then samples of their usage which made clear that all three are different and usage is disjoint at syntax level. As for DROP ROUTINE we could just raise an error and recommend using more specific DROP FUNCTION or DROP PROCEDURE if there is ambiguity. -------------- Best Regards Hannu
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
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
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.
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.