Test and document the behavior of initialization cross-refs in plpgsql.

tglsfdc · tglsfdc · commit a2a731d6c9db · 2021-10-29T12:45:33.000-04:00
We had a test showing that a variable isn't referenceable in its own initialization expression, nor in prior ones in the same block. It *is* referenceable in later expressions in the same block, but AFAICS there is no test case exercising that. Add one, and also add some error cases. Also, document that this is possible, since the docs failed to cover the point. Per question from tomás at tuxteam. I don't feel any need to back-patch this, but we should ensure we don't break it in future. Discussion: https://postgr.es/m/20211029121435.GA5414@tuxteam.de
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
@@ -379,7 +379,17 @@ arow RECORD;
 <programlisting>
 quantity integer DEFAULT 32;
 url varchar := 'http://mysite.com';
-user_id CONSTANT integer := 10;
+transaction_time CONSTANT timestamp with time zone := now();
+</programlisting>
+     </para>
+
+     <para>
+      Once declared, a variable's value can be used in later initialization
+      expressions in the same block, for example:
+<programlisting>
+DECLARE
+  x integer := 1;
+  y integer := x + 1;
 </programlisting>
      </para>
 
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
@@ -4637,24 +4637,50 @@ NOTICE:  caught division by zero
 NOTICE:  caught division by zero
 NOTICE:  caught division by zero
 -- Check variable scoping -- a var is not available in its own or prior
--- default expressions.
-create function scope_test() returns int as $$
+-- default expressions, but it is available in later ones.
+do $$
+declare x int := x + 1;  -- error
+begin
+  raise notice 'x = %', x;
+end;
+$$;
+ERROR:  column "x" does not exist
+LINE 1: x + 1
+        ^
+QUERY:  x + 1
+CONTEXT:  PL/pgSQL function inline_code_block line 3 during statement block local variable initialization
+do $$
+declare y int := x + 1;  -- error
+        x int := 42;
+begin
+  raise notice 'x = %, y = %', x, y;
+end;
+$$;
+ERROR:  column "x" does not exist
+LINE 1: x + 1
+        ^
+QUERY:  x + 1
+CONTEXT:  PL/pgSQL function inline_code_block line 4 during statement block local variable initialization
+do $$
+declare x int := 42;
+        y int := x + 1;
+begin
+  raise notice 'x = %, y = %', x, y;
+end;
+$$;
+NOTICE:  x = 42, y = 43
+do $$
 declare x int := 42;
 begin
   declare y int := x + 1;
           x int := x + 2;
+          z int := x * 10;
   begin
-    return x * 100 + y;
+    raise notice 'x = %, y = %, z = %', x, y, z;
   end;
 end;
-$$ language plpgsql;
-select scope_test();
- scope_test 
-------------
-       4443
-(1 row)
-
-drop function scope_test();
+$$;
+NOTICE:  x = 44, y = 43, z = 440
 -- Check handling of conflicts between plpgsql vars and table columns.
 set plpgsql.variable_conflict = error;
 create function conflict_test() returns setof int8_tbl as $$
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
@@ -3795,22 +3795,42 @@ end;
 $outer$;
 
 -- Check variable scoping -- a var is not available in its own or prior
--- default expressions.
+-- default expressions, but it is available in later ones.
 
-create function scope_test() returns int as $$
+do $$
+declare x int := x + 1;  -- error
+begin
+  raise notice 'x = %', x;
+end;
+$$;
+
+do $$
+declare y int := x + 1;  -- error
+        x int := 42;
+begin
+  raise notice 'x = %, y = %', x, y;
+end;
+$$;
+
+do $$
+declare x int := 42;
+        y int := x + 1;
+begin
+  raise notice 'x = %, y = %', x, y;
+end;
+$$;
+
+do $$
 declare x int := 42;
 begin
   declare y int := x + 1;
           x int := x + 2;
+          z int := x * 10;
   begin
-    return x * 100 + y;
+    raise notice 'x = %, y = %, z = %', x, y, z;
   end;
 end;
-$$ language plpgsql;
-
-select scope_test();
-
-drop function scope_test();
+$$;
 
 -- Check handling of conflicts between plpgsql vars and table columns.
 
