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

I knew about this project since its beginning and I think I understand most
of its background and internals. Below are my comments (sorry for
duplication of some thoughts already posted).

First of all, I agree that the current meaning of an UDF should be
deprecated. The server supports procedures and functions that may be either
internal or external. By internal I mean PSQL. The prototype implementation
of PSQL functions is in my private tree for some months already (SQL99
syntax and semantics are supported). External [native binary] functions
exist for a long time, external procedures (i.e. "void" external functions)
is a trivial task to implement.

Now we have a design specification for another kind of external routines -
Java ones. I suggest to follow one rule - distinguish between internal and
external routines, both semantically and syntactically. It means that
internal routines are part of a database and they are CREATEd (and also
ALTERed) there, whilst external ones are only DECLAREd. Obviously, internal
routines are stored inside a database, whilst external ones - outside. It
allows to (a) preserve the current DDL syntax and (b) clearly understand
what is what. Then it becomes obvious that we now have two types of external
routines and we need to separate them. More types may be expected to appear
in the future. Three things required for that are:

1) some symbolic qualifiers (like the proposed {JAVA | NATIVE} ones) in the
DDL syntax
2) new field (e.g. RDB$MODULE_TYPE) in RDB$FUNCTIONS
3) execution subsytems for different types of external routines

As we're unifying the things, it might make sense to merge both
RDB$PROCEDURES and RDB$FUNCTIONS into one system table.

I agree that external routines (including current external functions) should
have an ability to callback the engine, either preserving the request
context (att_handle and tra_handle) or not. And no doubt that Jim's
Jdbc-like interface fits best into this requirement.

But I tend to disagree regarding selectable procedures. I see no reason to
separate selectable and executable ones. In fact, if a procedure contains
one and only one SUSPEND, it's both selectable and executable. This whole
issue is perhaps quite tricky and sometimes badly understood by newbies, but
it gives some power to those who know how the things work. Below is a phrase
from your paper:

"Only procedures marked with SELECTABLE keyword can be used only in SELECT
statement. EXECUTE PROCEDURE statement is used for non-selectable
procedures."

The first sentense can be made true without breaking anything (select from
non-selectable procedure always returns a NULL resultset, so nobody should
use such a code), but the second one is a real problem. And if this issue
cannot be solved completely, I tend to think that any partial solution will
only cause more confusion to end-users.

Now regarding the following statement "It should be possible to call
procedures that return only one output parameter as functions, e.g. SELECT
myProc(someCol) FROM someTable should be valid SQL statement". I'd prefer to
declare such functions explicitly, via DECLARE EXTERNAL FUNCTION. It's not a
matter of the parser only, because stored (i.e. internal) functions are
required by the SQL specification to have slightly different syntax than
stored procedures, so there won't be any confusion there. But if we clearly
distinguish between internal procedures and functions, why making external
ones to behave separately? Our goal is to make the difference (from the
engine's POV) between internal and external routines as much transparent as
possible.

I didn't follow closely the interface details posted in the proposal, but
generally I agree with all the points. As for the selectable procedures
implementation, I'd prefer the first option proposed.

About the internal routines... I see Paul is keen to detach the entire PSQL
implementation from the engine and make it an independent (although still
built-in) subsystem under the aforementioned interface. I don't have strong
pro's or contra's at the time being and would like to discuss these ideas
later, once the external stuff is agreed on.

And, finally, I'd like to thank Eugene, Paul and Roman for their efforts in
this area.


Dmitry