1

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)

Improve this question
2
  • The inner DO blocks makes absolutely no sense to me. What are you trying to achieve with that? What is the underlying problem you are trying to solve here? Commented Apr 16, 2022 at 8:59
  • I'm trying to group specific sub tasks into smaller named blocks with a defined purpose to structure and organize the entire logic, be able to read the whole thing more easily when I go back to it later, creating functions for these one-time tasks wouldn't be worth it. If you have better suggestions for structuring the code, those are welcome, but I would still like to know if what I'm trying to do is possible, regardless of wether it is or not the best approach Commented Apr 16, 2022 at 15:07

1 Answer 1

Reset to default
3

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:

  1. 'test' instead of "test" so the value is not seen as an identifier.

  2. Using the <<outerblock>> label syntax to label the outer block. The $<some_tag>$ does not do this.

  3. 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.

FURTHER UPDATE

You can have a nested DO function:

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

    DO
    $i$
    DECLARE
        inner_test_variable text := 'inner test';
    BEGIN
        RAISE NOTICE 'Inner %', inner_test_variable;

    END;
    $i$;
END;
$$;

NOTICE:  test
NOTICE:  Inner inner test
DO

You just can't access the variables in the outer portion. They would have to be passed into the inner function as a parameter to a function argument and DO functions do not have arguments. You could do it with a regular function.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I don't see the inner block's "DO", did you forget to type it or are you doing something here that eludes me, I see you did type "DECLARE" and 'BEGIN' twice though
There is no need for the DO.
See my UPDATE for more.
I am not trying to do nested functions, I really am trying to just have nested blocks, and in fact I am trying to put them inside a function just like in the link you provided, I didn't know the DO for the inner block was unnecessary, thanks this lets me do exactly what I was trying to do
Glad it worked. Another FYI, per Identifiers I would stay away from using - as in outer-block and inner-block. Use _ instead. Using - means you have to quote the identifier when you create it and whenever you use it.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.