Hi Helen,

thanks for this post. I haven't encountered too much guidance on
writing Firebird-PSQL yet, like best practices and so on. I'd be
thankful for any recommendations anyone might have in that regard,
like pointers to documentation. The chapters on "Server Programming"
in your book are good, but there's room for more specialized advice.

I also have a couple of questions with respect to your explanations.
Please see my comments inline.

Helen Borrie schrieb am 04.12.2010 um 15:12 (+1300):

>
> Code it as a selectable procedure if you plan to return a set of
> zero to n rows to the client.
>
> > or should I use always the execute procedure syntax
>
> Code it as an executable procedure using returning_val when
> a) you want to return a set of zero or one rows to the client

That's a good guideline, easy to remember.

> b) you want to return values from a sub-procedure back to local
> variables in a calling procedure

What's the rationale for preferring an executable SP in this
scenario?

> Understand what SUSPEND actually *does*. It sends a result row
> out to the client buffer and waits for the client app to fetch the
> row. Execution of the SP is *suspended* until the waiting row has
> been fetched.

This sounds like some IPC, possibly even a network transaction, is
needed for each row. But is that so? In your book on pp.617, 618
(schema) and 620, you mention the "row cache". My understanding was
that rows are buffered there and fetched in batches, not unlike the
way an IO buffer works. But I may well have misunderstood.

> Furthermore, there are some good reasons why you shouldn't write
> SPs that both change data and return sets.

What are those reasons? I can think of user expectation. Are there
other reasons?

> >create procedure MYPROC(
> > IN_PORTION float)
> >returns (
> > HALF_PORTION float)
> >as
> >begin
> > HALF_PORTION = IN_PORTION / 2;
> > suspend;
> >end
> >
> >usage as (SELECTABLE) STORED PROCEDURE:
> >execute procedure MYPROC( PARAM) returning_values :RESULT
>
> This is not usage as a selectable SP. The SUSPEND in this
> procedure, when invoked with EXECUTE PROCEDURE, is a no-op
> (actually, it just behaves as EXIT).

Except for (at least) one aspect: Omitting SUSPEND will deny you the
possibility to SELECT from the procedure. Which makes me think that
a *selectable* procedure (one you can SELECT from) is not necessarily
one that contains a SELECT itself, but rather one that contains a
SUSPEND statement.

So a selectable procedure is a SUSPEND-procedure. With the proviso
that in cases such as above, as you point out, using SUSPEND is not
recommended; the recommended way in this case, IIUC, is to just call
EXECUTE PROCEDURE and not to think SELECT would be nicer. Correct?

> Your procedure, as written, will get you a scalar result back to
> the client, by simply selecting, viz.
>
> select result from MYPROC(PARAM);
>
> -- which could be used in a subquery, e.g.,
> select
> column1,
> column2,
> (select result from MYPROC(PARAM)) RESULT
> from aTable;
>
> >usage as SELECTABLE STORED PROCEDURE:
> >select * from MYPROC( 19.0 ) into :RESULT;
>
> Hmm, not a great idea. Sometimes it will work (as this one would,
> since it is scalar) but sometimes you will get unpleasant surprises.

A scalar, i.e. its return value is a single row, not a table; its
cardinality is 1; a fact which is not known at compile-time, so it
is prone to runtime errors.

In contrast to the cardinality, the degree (number of columns) of
the SP *is* known to the caller, so you'll get a useful error
message straight away:

-SQL error code = -313
-count of column list and variable list do not match

> SSPs are for returning multi-row sets to a client or (if you must)
> returning a multi-row set to a cursor (fairly inefficiently).

Ah, right, because there are no table-valued variables as of FB 2.5,
so other than cursors and tables (including global temporary tables)
there is no facility to receive the result of a SSP. Which is why
you're saying that they're really meant for the client, correct?

> >usage of a UDF
> >:RESULT = udf_myFunc( 19.0 );
>
> That is not a valid syntax. You can do something a bit like this
> in DSQL with a scalar SSP, viz.,
>
> select udf_myFunc( 19.0 ) RESULT from rdb$database;

This syntax doesn't work with a scalar SSP. I think you meant a
function here?

Regards,

Michael