Use variable from a previous DO clause

Question

I have a .sql script like this:

DO $$
    DECLARE     
    prev_count := (SELECT count(*) FROM ...);
END$$;

UPDATE [...]

DO $$
    DECLARE     
    cur_count := (SELECT count(*) FROM ...);
    BEGIN
    ASSERT cur_count = prev_count, 'Mismatch';
END$$;

In which I get some value, modify the database, and expect a new value to match an old value. However, I get errors like this:

psql:migration.sql:163: ERROR:  column "prev_count" does not exist
LINE 1: SELECT cur_count = prev_count
                              ^
QUERY:  SELECT cur_count = prev_count
CONTEXT:  PL/pgSQL function inline_code_block line 4 at ASSERT

I can't tell if this is a scoping issue because of the anonymous block, and why it's attempting to treat my variables like columns. Any ideas?

Looks like a scope issue and it is assuming prev_count is a column because the variable name prev_count no longer exists in the current scope. If this is a repeated task with a static update function I would probably just create a stored proc — BShaps
– BShaps, Commented May 16, 2018 at 18:14

Dan · Accepted Answer · 2018-05-16 18:29:37Z

2

According to the manual DO executes an anonymous code block that:

... is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time...

So it is a function that returns VOID. In that sense prev_count only exists in the first DO.

To avoid this, you could create a TEMP table and insert the prev_count in the first DO so you can use it anywhere in the transaction.

answered May 16, 2018 at 18:29

Dan

1,9211 gold badge16 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Use variable from a previous DO clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related