Re: [Firebird-devel] CALL statement

[Adriano d. S. F. <adr...@gm...>]

Hi!

I'd like to re-discuss this also taking into consideration named
arguments feature.

Named arguments is standard feature where arguments call be passed to
routines using => token, example:

routine_name(param1 => 1, param2 => 2)

Then back to standard CALL statement, it works also with named
parameters and it works with output parameters using SQL standard way of
output parameters, where a routine is defined with parameters using IN,
OUT or INOUT all mixed, so:

CALL routine_name(input_param1 => 1, output_param1 => ?, input_param2 => 2)

or

CALL routine_name(1, ?, 2) -- (routine_name was defined with IN
INPUT_PARAM1, OUT OUTPUT_PARAM1, IN INPUT_PARAM2 in this order)

We can make CALL work in Firebird using standard syntax, the question is
if we can make it or something else better. CALL with named parameters
is really good, but positional syntax not so much.

Considering that one created a Firebird procedure with these parameters:

Input:
- I1 mandatory
- I2 DEFAULT true

Output:
- O1
- O2

CALL proc_name(1, default, ?, ?)

I'm not sure this "default" is standard, but I would add it for
obviously reason to be able to use default parameters.

Maybe we just can say that CALL with positional syntax is not good with
many parameters.

Note that named arguments would also be added to EXECUTE PROCEDURE as
well function calls.

Therefore I would change my proposal to:
- Add named arguments - for EXECUTE PROCEDURE, function invocation and
CALL statement
- Add DEFAULT for argument passing
- Add standard CALL statement
- Maybe re-discuss (*) extension to CALL or EXECUTE PROCEDURE in another
proposal

* That would be because CALL is awful to return all output parameters
and also awful in the way it mix input parameters having defaults with
output parameters in positional syntax. And EXECUTE PROCEDURE is easy to
return all output parameters, but its syntax is awful in others ways
(many variants, too much verbose).


Adriano



On 31/05/2021 10:19, Adriano dos Santos Fernandes wrote:
> Hi!
> 
> EXECUTE PROCEDURE is full of weirdness, so I propose that standard SQL
> CALL is adapted for our needs.
> 
> EXECUTE PROCEDURE [ <package> . ] <name>
>     [ <value list> | ( <value list> ) ]
>     [ RETURNING_VALUES <variable list> |
>       RETURNING_VALUES ( <variable list> ) ]
> 
> It does not allow one to "select" what just it wants.
> 
> So if one changes the procedure output parameters, clients (DSQL and
> PSQL) needs to be changed. It's like "SELECT *" which is sure a bad
> practice.
> 
> It has this weird RETURNING_VALUES and multiple syntax about parenthesis.
> 
> It does not allow to just execute and ignore output parameters.
> 
> I propose that CALL syntax:
> 
> CALL [ <package> . ] <name> ( <value list> )
>     [ RETURNING { * | <value list> } ]
>     [ INTO <variable list> ]
> 
> A CALL without RETURNING and without INTO will execute the procedure and
> ignore possible output parameters.
> 
> A CALL without RETURNING and with INTO (PSQL only), works like with
> "RETURNING *" and EXECUTE PROCEDURE ... RETURNING_VALUES.
> 
> Examples:
> 
> -- Like EXECUTE PROCEDURE in PSQL
> CALL proc(1, 2) INTO v1, v2
> 
> -- Can ignore some output parameters in PSQL
> CALL proc(1, 2) RETURNING o1 INTO a1
> 
> -- Can ignore some output parameters in DSQL
> CALL proc(1, 2) RETURNING o1
> 
> -- Ignore all output parameters in DSQL and PSQL
> CALL proc(1, 2)
> 
> 
> Adriano