Peter Eisentraut <peter.eisentr...@enterprisedb.com> writes:
> On 25.05.21 17:20, Tom Lane wrote:
>> I don't really see how you can argue that the existing behavior is
>> more spec-compliant than what I'm suggesting. What I read in the spec
>> (SQL:2021 10.4 <routine invocation> SR 9) h) iii) 1)) is
>> 1) If Pi is an output SQL parameter, then XAi shall be a <target
>> specification>.
> I concede that the current implementation is not fully standards
> compliant in this respect. Maybe we need to rethink how we can satisfy
> this better. For example, in some other implementations, you write CALL
> p(?), (where ? is the parameter placeholder), so it's sort of an output
> parameter. However, changing it so that the entire way the parameters
> are counted is different seems a much greater departure.
I'd expect to be able to write something like that in contexts where
there's a reasonable way to name an output parameter. Like, say,
plpgsql. Or JDBC --- I think they already use a notation like that
for output parameters from functions, and transform it after the fact.
As things work in HEAD, they'll have to have a different special hack
for procedures than they do for functions. But none of this applies
to bare-SQL CALL.
>> More generally, there are enough deviations from spec in what we do
>> to perform ambiguous-call resolution that it seems rather silly to
>> hang your hat on this particular point.
> I don't know what you mean by this.
Well, let's take an example. If OUT parameters are part of the
signature, then I'm allowed to do this:
regression=# create procedure p1(in x int, out y int)
regression-# language sql as 'select $1';
CREATE PROCEDURE
regression=# create procedure p1(in x int, out y float8)
language sql as 'select $1';
CREATE PROCEDURE
regression=# call p1(42, null);
y
----
42
(1 row)
I'm surprised that that worked rather than throwing an ambiguity
error. I wonder which procedure it called, and where in the spec
you can find chapter and verse saying that that one and not the other
one is right.
It gets even sillier though, because experimentation shows that it
was the int one that was preferred:
regression=# create or replace procedure p1(in x int, out y float8)
language sql as 'select $1+1';
CREATE PROCEDURE
regression=# call p1(42, null);
y
----
42
(1 row)
That seems kind of backwards really, considering that float8 is
further up the numeric hierarchy. But let's keep going:
regression=# create procedure p1(in x int, out y text)
language sql as 'select $1+2';
CREATE PROCEDURE
regression=# call p1(42, null);
y
----
44
(1 row)
So text is preferred to either int or float8. I know why that
happened: we have a preference for matching UNKNOWN to string types.
But I challenge you to provide any argument that this behavior is
spec-compliant.
More generally, the point I'm trying to make is that our rules
for resolving an ambiguous function differ in a whole lot of
details from what SQL says. That ship sailed a couple of
decades ago, so I'm not excited about adopting a fundamentally
bad design in pursuit of trying to make one small detail of
that behavior slightly closer to SQL.
[ thinks a bit ]
A lot of what I'm exercised about here is not the question of
how many parameters we write in CALL, but the choice to redefine
proargtypes (and thereby change what is considered the routine's
signature). With the infrastructure in the patch I proposed,
it'd be possible to revert the signature changes and still
write dummy output parameters in CALL -- we'd just make CALL
set include_out_parameters=true all the time. I do not think that
solution is superior to what I did in the patch, but if we can't
have a meeting of the minds on CALL, doing that much would still
be an improvement.
regards, tom lane
