Can Firebird SQL procedure know the parent procedure/trigger from which it is called from?

Question

I have SQL procedure which should return a bit different result if it is called from one specific procedure. Is it possible for the SQL procedure to detect that it is called from one particular other SQL procedure?

Maybe monitoring mon$... table data can give the answer?

Question applied to Firebird 2.1

E.g. there is mon$call_stack table, but for mostly mon$... tables are empty for Firebird 2.1, they fill up for later versions of Firebird.

Answer 1

Hidden data dependencies are bad idea. There is a reason why programmers see "pure function" as a good thing to pursue. Perhaps not in all situations and not at all costs, but when other factors are not affected it better be so.

https://en.wikipedia.org/wiki/Pure_function

So, Mark is correct that if there is something that affects your procedure logic - then it better be explicitly documented by becoming an explicit function parameter. Unless your explicit goal was exactly to create a hidden backdoor.

This, however, mean that all the "clients" of that procedure, all the places where it can be called from, should be changed as well, and this should be done in concert, both during development and during upgrades at client deployment sites. Which can be complicated.

So I rather would propose creating a new procedure and moving all the actual logic into it.

https://en.wikipedia.org/wiki/Adapter_pattern

Assuming you have some

create procedure old_proc(param1 type1, param2 type2, param3 type3) as 
begin
   ....some real work and logic here....
end;

transform it into something like

create procedure new_proc(param1 type1, param2 type2, param3 type3, 
            new_param smallint not null = 0) as 
begin
   ....some real work and logic here....
   ....using new parameter for behavior fine-tuning...
end;

create procedure old_proc(param1 type1, param2 type2, param3 type3) as 
begin
  execute procedure new_proc(param1, param2, param3)
end;

...and then you explicitly make "one specific procedure" call new_proc(...., 1). Then gradually, one place after another, you would move ALL you programs from calling old_proc to calling new_proc and eventually you would retire the old_proc when all dependencies are moved to new API.

• https://www.firebirdsql.org/rlsnotesh/rnfbtwo-psql.html#psql-default-args

---

There is one more option to pass "hidden backdoor parameter" - that is context variables, introduced in Firebird 2.0

https://www.firebirdsql.org/rlsnotesh/rlsnotes20.html#dml-dsql-context

and then your callee would check like that

 .....normal execution
 if ( rdb$get_context('USER_TRANSACTION','my_caller') is not null) THEN BEGIN
      ....new behavior...
 end;

However, you would have to make that "one specific procedure" to properly set this variable before calling (which is tedious but not hard) AND properly delete it after the call (and this should be properly framed to properly happen even in case of any errors/exceptions, and this also is tedious and is not easy).

Answer 2

I'm not aware of any such option. If your procedure should exhibit special behaviour when called from a specific procedure, I'd recommend that you make it explicit by adding an extra parameter specifying the type of behaviour, or separating this into two different procedures.

That way, you can also test the behaviour directly.

Answer 3

Although I agree that the best way would probably be to add a parameter to the procedure to help identify where it is being called from, sometimes we don't have the luxury for that. Consider the scenario where the procedure signature can't change because it is in a legacy system and called in many places. In this scenario I would consider the following example;

The stored procedure that needs to know who called it will be called SPROC_A in this example.

First we create a Global Temp Table

CREATE GLOBAL TEMPORARY TABLE GTT_CALLING_PROC
   ( PKEY INTEGER primary key,
   CALLING_PROC VARCHAR(31))
   ON COMMIT DELETE ROWS;

Next we create another Stored procedure called SPROC_A_WRAPPER that will wrap the calling to SPROC_A

CREATE OR ALTER PROCEDURE SPROC_A_WRAPPER
AS
DECLARE CALLING_SPROC VARCHAR(31);
BEGIN

  DELETE FROM GTT_CALLING_PROC
  WHERE GTT_CALLING_PROC.PKEY = 1;

  INSERT INTO GTT_CALLING_PROC (
      PKEY,
      CALLING_PROC)
  VALUES (
      1,
      'SPROC_A_WRAPPPER');

  EXECUTE PROCEDURE SPROC_A;

  DELETE FROM GTT_CALLING_PROC
  WHERE GTT_CALLING_PROC.PKEY = 1;

END

and finally we have SPROC_A

CREATE OR ALTER PROCEDURE SPROC_A
AS
DECLARE CALLING_SPROC VARCHAR(31);
BEGIN

  SELECT FIRST 1 CALLING_PROC
  FROM GTT_CALLING_PROC
  WHERE GTT_CALLING_PROC.PKEY = 1
  INTO :CALLING_SPROC;

  IF (:CALLING_SPROC = 'SPROC_A_WRAPPER') THEN
  BEGIN
    /*  Do Something  */
  END
  ELSE
  BEGIN
    /*  Do Something Else */
  END
END

The SPROC_A_WRAPPER will populate the Temp table, call that SPROC_A and then delete the row from the Temp Table, in case SPROC_A is called from someplace else within the same transaction, it won't think SPROC_A_WRAPPER called it.

Although somewhat crude, I believe this would satisfy your need.