Accessing outer block variables from within a nested block-CodePudding

I am trying to access a variable from a parent block from within a nested block using postgresql's plpgsql as follows

DO $outer-block$
DECLARE
  test_variable text DEFAULT "test";
BEGIN

  DO $inner-block$
  BEGIN

    RAISE NOTICE '%',test_variable;

  END;
  $inner-block$;

END;
$outer-block$;

When I try to run this, it tells me that "the column test_variable doesn't exist", so I suppose that this variable is not in scope when the inner block is given control, is there any way to access it from within the nested block?

(I checked up this question: How to access outer scope variables from a function in PostgreSQL?, which was sort of similar, but in that case it's trying to access the outer block variable from whitin a function, which is a persistent database object, and it would in a way be trying to create a closure, that is not what I am doing here, since I want to access the outer block's scope from within a one-time execution inner block, which might make a difference, but I don't know it for certain)

CodePudding user response：

The documentation plpgsql structure shows you how to do this. Using your example as starting point:

DO
$$
<<outerblock>>
DECLARE
  test_variable text DEFAULT 'test';
BEGIN
    RAISE NOTICE '%',test_variable;

    DECLARE
        test_variable text := 'inner test';
    BEGIN
        RAISE NOTICE '%',test_variable;
        RAISE NOTICE '%', outerblock.test_variable;

  END;

END;
$$;

NOTICE:  test
NOTICE:  inner test
NOTICE:  test

Differences from your example:

'test' instead of "test" so the value is not seen as an identifier.
Using the <<outerblock>> label syntax to label the outer block. The $<some_tag>$ does not do this.
Using the label in outerblock.test_variable to fetch the variable value from the outer block.

FYI, in your original example there is no need for the inner block as you don't do anything that changes the value test_variable so it will be the same as the outer block anyway.

UPDATE

Just realized you maybe trying to do nested functions, per the use of DO in your example. That is a different thing then blocks. In order to pass data you would have to add it as a parameter to an argument in the nested function. Since DO functions can't have arguments that is not going to work. You would have to use a regular function.