Re: SQL-Invoked Procedures for 8.1

Tom, Gavin, Peter, Andrew,

> [ thinks some more... ] Actually I guess the problem comes with
>
> create function foo(i float, j int) ...
> create function foo(j int, i float) ...
>
> which is a legal pair of functions from a positional viewpoint, but
> would look identical when matching by names. We'd have to think of some
> way to forbid that.

Actually, I don't think we have to forbid it at function/SP creation time.
We already tolerate a certain level of ambiguity thanks to polymorphics.
For example:

primer=# create function ambiguous(anyelement) returns text as ' select
cast($1 as text); ' language sql;
CREATE FUNCTION
primer=# create function ambiguous(anyarray) returns text as ' select
array_to_string($1, '' ''); ' language sql;
CREATE FUNCTION
primer=# select ambiguous(ARRAY[1, 2, 3, 4]);
ERROR: function ambiguous(integer[]) is not unique
HINT: Could not choose a best candidate function. You may need to add
explicit type casts.

I don't see why we can't extend this idea to named parameter calls. If the
user's call is ambiguous, then say so and throw and error. This could even
allow the creation of default params if we just establish a search order:
1) matches same params, same (default) types, same order;
2) matches same params, compatible types, same order;
3) matches same params with compatible types, different order;
4) matches more params if extras are DEFAULT.

Thus, a call of:
CALL sp_ambiguous ( j as 1, k as 5.0 )

Would match:
sp_ambiguous ( j INT, k FLOAT )
before it would match:
sp_ambiguous ( j FLOAT, k INT )
and before it would match:
sp_ambiguous ( k NUMERIC, j INT )
and before it would match:
sp_ambiguous ( k NUMERIC, j BIGINT, m TEXT DEFAULT 'Nothing' );

Obviously, this whole "search pattern" would take time, so it should only
happen when the user makes a named parameter call and, NOT for strictly
ordered parameter calls. Then we'd document that there is a performance
difference between the two.

> The main thing that I'm not happy about is the syntax. I'm going to
> resist commandeering => for this purpose, and I don't see any way to use
> that symbol for this without forbidding it as a user-defined operator.
> I previously suggested using AS, which is already a fully reserved word,
> but that suggestion seems not to have garnered any support.

I don't remember seeing it. I'm perfectly happy with AS; it solves a lot of
problems that = or => would cause.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco