How to nest anonymous blocks with declare statements in Firebird?

Question

In Firebird, DECLARE statements can be listed at the beginning of an EXECUTE BLOCK statement:

EXECUTE BLOCK [(<inparams>)]
     [RETURNS (<outparams>)]
AS
   [<declarations>]
BEGIN
   [<PSQL statements>]
END

Within a block, no further DECLARE statements are possible, i.e. all variables are globally scoped, for the entire block. Is there any workaround to declare local variables for the scope of a nested block only, in Firebird? Nesting EXECUTE BLOCK calls isn't possible:

EXECUTE BLOCK AS
  DECLARE i INTEGER;
BEGIN
  EXECUTE BLOCK AS -- This doesn't compile
    DECLARE j INTEGER;
  BEGIN

  END
END

yes, there is a work-around: you can bail our of Programming SQL back into Dynamic SQL using `EXECUTE STATEMENT` - i once had to do it for the sake of quasi-generic programming. But that was one and the only type and i would not suggest you doing that. If your function is so very long, that justifies needing nested namespaces - then just turn it into an explicit and named Stored Procedure — Arioch 'The, Mar 12 '20 at 13:24
@Arioch'The: I had thought of the `EXECUTE STATEMENT` approach, which is viable in my case, but difficult. I'm writing framework logic, not specific procedural code, so I can't change the code itself. — Lukas Eder, Mar 12 '20 at 13:36
but why can't a "framework" create named SPs just like it can create named tables and views ? Like WWW servers, that can be purely static, purely dynamic, or lazy-static (persistently caching). Can framework be creating/updating needed SPs then using them ? — Arioch 'The, Mar 13 '20 at 10:17
@Arioch'The: It could, but it would be *very* laborious, and in all other dialects so far, nesting anonymous block was not an issue at all. So I'm looking for the path of least resistance — Lukas Eder, Mar 13 '20 at 11:32
Well, okay, then why you would really need isolated namespaces? maybe it will be easier to make ad hoc unique prefixes to names and then lift up the declarations? Your framework module generates the "nested block" text, and passes it up with metadata like "everything starting with `_~1~_` is local identifier". The "combining before executing" function then assigns unique prefixes to each of building blocks, does simple text replace and lifts declarations. This would work on FB 2 as well as FB 3. Granted, if you will fit into 64KB limit of one SQL command text length. — Arioch 'The, Mar 13 '20 at 12:36

Mark Rotteveel · Answer 1 · 2020-03-12T15:14:03.120

You can't nest execute block statements. The design of this feature did not consider this option. In Firebird 3, you can define 'sub-procedures' or 'sub-functions', but those don't have access to variables or input columns from the outer-level (so they only provide an isolated scope, and you'd need to explicitly pass in values using parameters, and out using returning values), and you can't nest more than one level.

For example:

execute block
  returns (firstval integer, secondval integer)
as
declare function x(param1 integer) returns integer
  as
  declare var1 integer;
  begin
    var1 = param1 * 2;
    return param1 + var1;
  end
begin
  firstval = 1;
  while (firstval < 10) do
  begin
    secondval = x(firstval);
    suspend;
    firstval = firstval + 1;
  end
end

https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=677b33e416bd3f6a6a34e060d9afce9e

I am not aware of other options to declare variables with a limited scope in Firebird PSQL.

That's... wonderful :) About as good as using `EXECUTE STATEMENT` in my case. I could generate this code in jOOQ, but maybe, I'll wait for someone requesting this first... Thanks for the effort! — Lukas Eder, Mar 12 '20 at 16:45

How to nest anonymous blocks with declare statements in Firebird?

1 Answers1