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