Re: CALL versus procedures with output-only arguments

Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> On 26.05.21 19:28, Tom Lane wrote:
>> Why?  If it actually works that way right now, I'd maintain
>> strenously that it's broken.  The latter should be referring
>> to a procedure with two IN arguments.  Even if the SQL spec
>> allows fuzziness about that, we cannot afford to, because we
>> have a more generous view of overloading than the spec does.

> There is no fuzziness in the spec about this.  See subclause <specific 
> routine designator>.  It just talks about arguments, nothing about input 
> or output arguments.  I don't find any ambiguity there.  I don't see why 
> we want to reinvent this here.

I agree that the spec isn't ambiguous: it says that you should be able
to uniquely identify a routine from the list of only its argument types,
without distinguishing whether those arguments are IN or OUT or INOUT,
*and* without distinguishing whether the routine is a procedure or
function.

However, that doesn't work for Postgres functions, nor for Postgres
routines (since those must include functions).  I do not think that we
should confuse our users and effectively break ALTER/DROP ROUTINE in
order to make it sort-of work for procedures.  The are-we-exactly-
compatible-with-the-spec ship sailed a couple of decades ago.

> If I have two procedures
> p1(IN int, IN int, OUT int, OUT int)
> p1(OUT int, OUT int)
> then a DROP, or ALTER, or GRANT, etc. on p1(int, int) should operate on 
> the second one in a spec-compliant implementation, but you propose to 
> have it operate on the first one.  That kind of discrepancy would be 
> really bad to have.

We already have that situation for functions.  I think having procedures
work differently from functions is much worse than your complaint here;
and I do not see why being spec-compliant for one case when we are not
for the other is a good situation to be in.

We could, perhaps, insist that ALTER/DROP include OUT parameters when
it is being applied to a procedure, rather than treating them as being
effectively noise words as we do now.  I'd still want to revert the
definition of proargtypes, which would have implications for which
procedure signatures are considered distinct --- but it looks to me
like we would still be allowing more combinations than the spec does.

>> (As far as I could tell from looking at the spec yesterday,
>> they think that you aren't allowed to have two procedures
>> with the same name/schema and same number of arguments,
>> regardless of the details of those arguments.  Up with that
>> I will not put.)

> I don't see that.

It's under CREATE PROCEDURE.  11.60 <SQL-invoked routine> SR 20 says

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.

Case b) has different and laxer rules for what you can do with functions,
but it still looks like they'd forbid a lot of situations that we allow.

I think that these restrictive overloading rules have a whole lot to do
with the fact that they feel that you don't need IN/OUT argument labeling
to correctly identify a function or procedure.  But, as I said, that ship
sailed for us a long time ago.

            regards, tom lane