"Roman Rokytskyy" <rrokytskyy@...> wrote:

>
> That's what I also proposed with CALL. The only reason to introduce
> new keyword is to keep the old behavior untouched. But if this is not
> a problem, I vote with two hands to extend EXECUTE PROCEDURE to be
> able to also execute selectable procedures correctly.

Regardless of my own opinion about your request <g>, I see a few ways to
help you.

1) Extend EXECUTE PROCEDURE to return a result set for selectable
procedures.

We're not MSSQL, so a single row will still be returned in PSQL (not an
implicit cursor), so no problems here. I'd expect something like FOR EXECUTE
PROCEDURE in PSQL to return a cursor, like we now do with EXECUTE STATEMENT.
A simple SELECT will stay an optional way of doing this. But we have a
problem with API - whether isc_info_sql_stmt_exec_procedure or
isc_info_sql_stmt_select is going to be returned during prepare? Any of the
answers will break the existing applications.

2) Throw an error from SELECT if it's called for a non-selectable procedure.

It will break a lot of existing databases and it's very wrong decision from
the design POV.

3) Add an optional keyword (e.g. RETURNING ROWS) to the DDL syntax, store it
in RDB$PROCEDURE_TYPE, change behaviour of EXECUTE PROCEDURE to be described
as either select or execute depending on this value. 100% compatibility +
everything is controlled by the developer. But a bit complex to handle
reliably.

4) Add CALL statement (BTW, this is a standard syntax for procedures, see
SQL-99) which cares about output parameters and return either a single set
of outputs or a cursor and hence will be described as either select or
execute. But what should be a PSQL syntax to retrieve a cursor? Or will this
be a client-only thingy?

BTW, we have exactly this issue with EXECUTE BLOCK - should the client
execute it or select from it? AFAIU, Vlad implemented it the expected way -
if output parameters exist, then it's a select statement (possibly returning
empty set), otherwise it's a procedure call. Since we have no legacy
behaviour, it was easy. The option (4) from the above suggests exactly the
same behaviour.

Did I miss anything?


Dmitry